INSERT INTO sales (barcode, purchase_order, order_no, line_no, line_code, line_description, item_no, tag_no, quantity, line_value, total_value, customer_code, project_name, works_month,
month_year_contractual, order_date, contractual_date, delivery_date, created_by, premium_order, component, outstanding_body, outstanding_diffuser, outstanding_disc, hold, packed_date, misc_ref_1, misc_ref_2, insert_date, updated_date)
(
SELECT w.*
FROM OPENQUERY(Welcom,'
SELECT
''5'' || numeric_order_no || line_no as "body_barcode",
purchase_order as "purchase_order",
order_no as "order_no",
line_no as "line_no",
line_code as "line_code",
line_description as "line_description",
item_no as "item_no",
tag_no as "tag_no",
quantity as "quantity",
line_value as "line_value",
total_value as "total_value",
customer_code as "customer_code",
project_name as "project_name",
works_month as "works_month",
month_year_contractual as "month_year_contractual",
order_date as "order_date",
contractual_date as "contractual_date",
delivery_date as "delivery_date",
created_by as "created_by",
premium_order as "premium_order",
''Body'' as "component",
outstanding_body as "outstanding_body",
outstanding_diffuser as "outstanding_diffuser",
outstanding_disc as "outstanding_disc",
hold as "hold",
packed as "packed",
misc_ref_1 as "misc_ref_1",
misc_ref_2 as "misc_ref_2",
syncronized as "inserted_date",
syncronized as "updated_date"
FROM
(
SELECT
TRIM(UPPER(O.CUSTOMR_RF)) as purchase_order,
TRIM(UPPER(L.ORDER_NUM)) as order_no,
TRIM(UPPER(L.ORDER_LINE)) as line_no,
TRIM(UPPER(L.PROD_CD)) as line_code,
TRIM(UPPER(S.PROD_DESC)) as line_description,
TRIM(UPPER(L.CUST_ITEM_NO)) as item_no,
TRIM(UPPER(L.TAG_NO)) as tag_no,
TRIM(UPPER(L.QTY_ORD)) as quantity,
TRIM(UPPER(L.C_UNIT_PRC)) as line_value,
TRIM(UPPER(L.H_TOT_VAL)) as total_value,
TRIM(UPPER(O.CUSTOMR_ID)) as customer_code,
TRIM(UPPER(O.PROJECT_NAME)) as project_name,
TRIM(UPPER(''holder'')) as works_month,
TRIM(UPPER(''1'')) as month_year_contractual,
TRIM(UPPER(TO_CHAR(O.ORDER_DTE,''DD/MM/YYYY''))) as order_date,
TRIM(UPPER(TO_CHAR(L.PROM_DTE,''DD/MM/YYYY''))) as contractual_date,
TRIM(UPPER(TO_CHAR(L.DLV_DTE,''DD/MM/YYYY''))) as delivery_date,
TRIM(UPPER(O.CREATED_BY)) AS created_by,
TRIM(UPPER(''HOLDER'')) AS premium_order,
TRIM(UPPER(''HOLDER'')) AS component,
TRIM(UPPER(''MATERIAL HOLDER'')) AS outstanding_body,
TRIM(UPPER(''MATERIAL HOLDER'')) AS outstanding_diffuser,
TRIM(UPPER(''MATERIAL HOLDER'')) AS outstanding_disc,
'''' as hold,
TRIM(UPPER(''PACKED'')) AS packed,
TRIM(UPPER(L.MISC_REF1)) AS misc_ref_1,
TRIM(UPPER(L.MISC_REF2)) AS misc_ref_2,
(select to_char(sysdate, ''DD-MM-YYYY HH24:MI:SS'') from dual) as syncronized,
REPLACE(TRANSLATE(UPPER(L.ORDER_NUM),''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ,./;''''[]\`~!@#$%^\*()__+{}-|?><'',''0123456789''), '' '', '''') AS numeric_order_no
FROM OPORDLN L
LEFT JOIN OPORDHD O ON TRIM(UPPER(L.ORDER_NUM)) = TRIM(UPPER(O.ORDER_NUM))
LEFT JOIN STPRDDSC S ON TRIM(UPPER(S.PROD_CODE)) = TRIM(UPPER(L.PROD_CD))
WHERE TRIM(PROD_CD) LIKE ''NZ'' || ''%'' OR TRIM(PROD_CD) LIKE ''NB'' || ''%''
AND UPPER(TRIM(L.ORDER_NUM)) LIKE ''AX'' || ''%''
)
WHERE length(numeric_order_no)=5
order by order_no ASC
') w
LEFT JOIN sales s on w.order_no = s.order_no
WHERE w.order_no +'-'+ w.line_no NOT IN (SELECT order_no+'-'+line_no FROM sales)
AND w.body_barcode NOT IN (select s.barcode from sales)
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE