SELECT okha.sts_code,okha.scs_code,okha.date_approved,okha.estimated_amount,
xscb.price_list_id, xscb.customer_bill_to_number, xscb.customer_ship_to_number,
xscb.contract_number, xscb.contract_type, xscb.cognomen, xscb.start_date,
xscb.end_date, qslh.NAME AS price_list_name,
ood.organization_code AS inventory_org_code,
hou.NAME AS operating_unit_name, customer, xscb.payment_term,
NULL price_list, line_item,
DECODE (TRIM (xscb.err_msg), ‘/’, ‘SUCCESS’, xscb.err_msg) err_msg,
xscb.request_id, line_cust_po_number, line_billing_freq,
DECODE (NVL(process_flag,’ ‘), ‘Y’, ‘PROCESSED’, ‘NOT PROCESSED’) status,
(select trx_number from ra_customer_trx_all where ct_Reference=to_Char(xscb.contract_number)) as invoice_number,
(select sum(extended_amount) from ra_customer_Trx_lines_All where sales_order=to_Char(xscb.contract_number)) as invoice_amount
–xxfadv_sc_ar_inf_pkg.get_number (xscb.) as invoice_line_number
FROM xxfadv_service_contract_stg xscb,
qp_secu_list_headers_vl qslh,
hr_operating_units hou,
org_organization_definitions ood,
okc_k_headers_all_b okha
WHERE 1 = 1
AND xscb.price_list_id = qslh.list_header_id(+)
AND hou.organization_id(+) = xscb.operating_unit_id
AND ood.organization_id(+) = inv_org_id
AND TO_CHAR (XSCB.CONTRACT_NUMBER) = okha.CONTRACT_NUMBER(+)
AND NVL(process_flag,’ ‘) =’Y’
–AND XSCB.CONTRACT_NUMBER IS NOT NULL
–AND XSCB.CONTRACT_NUMBER = ‘100097’
–AND XSCB.REQUEST_ID = :P_REQUEST_ID
AND XSCB.REQUEST_ID = NVL(:P_REQUEST_ID, (SELECT MAX(REQUEST_ID) FROM XXFADV_SERVICE_CONTRACT_STG))
Union all
SELECT null sts_code,null scs_code,NULL date_approved,null estimated_amount,
xscb.price_list_id, xscb.customer_bill_to_number, xscb.customer_ship_to_number,
xscb.contract_number, xscb.contract_type, xscb.cognomen, xscb.start_date,
xscb.end_date, qslh.NAME AS price_list_name,
ood.organization_code AS inventory_org_code,
hou.NAME AS operating_unit_name, customer, xscb.payment_term,
NULL price_list, line_item,
DECODE (TRIM (xscb.err_msg), ‘/’, ‘SUCCESS’, xscb.err_msg) err_msg,
xscb.request_id, line_cust_po_number, line_billing_freq,
DECODE (NVL(process_flag,’ ‘), ‘Y’, ‘PROCESSED’, ‘NOT PROCESSED’) status,
(select trx_number from ra_customer_trx_all where ct_Reference=to_Char(xscb.contract_number)) as invoice_number,
(select sum(extended_amount) from ra_customer_Trx_lines_All where sales_order=to_Char(xscb.contract_number)) as invoice_amount
–xxfadv_sc_ar_inf_pkg.get_number (xscb.) as invoice_line_number
FROM xxfadv_service_contract_stg xscb,
qp_secu_list_headers_vl qslh,
hr_operating_units hou,
org_organization_definitions ood
WHERE 1 = 1
AND xscb.price_list_id = qslh.list_header_id(+)
AND hou.organization_id(+) = xscb.operating_unit_id
AND ood.organization_id(+) = inv_org_id
AND NVL(process_flag,’ ‘)!=’Y’
–AND XSCB.CONTRACT_NUMBER IS NOT NULL
–AND XSCB.CONTRACT_NUMBER = ‘100097’
–AND XSCB.REQUEST_ID = :P_REQUEST_ID
AND XSCB.REQUEST_ID = NVL(:P_REQUEST_ID, (SELECT MAX(REQUEST_ID) FROM XXFADV_SERVICE_CONTRACT_STG))
Recent Posts