[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hzcas.cust_acct_site_id
FROM hz_cust_site_uses_all hzcsu ,
hz_cust_acct_sites_all hzcas
WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
AND hzcsu.site_use_id = pn_site_use_id
AND hzcas.cust_account_id = pn_customer_id ;
SELECT vat_Reg_no
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = pn_customer_id
AND address_id = pn_address_id;
SELECT 1
FROM
wsh_delivery_details wdd ,
wsh_new_deliveries wnd ,
wsh_delivery_assignments wda
WHERE
wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.Delivery_Id = wnd.Delivery_Id AND
wnd.Delivery_Id = cp_delivery_id AND
wdd.source_code = 'OE' AND
NVL(wdd.inv_interfaced_flag,'N') <> 'Y';
SELECT gst_inv_gen_status
FROM JAI_RGM_GST_INVOICE_GEN_T
WHERE delivery_id = NVL(cp_delivery_id, -1)
OR order_line_id = NVL(cp_order_line_id, -1);
:= 'SELECT delivery_id , delivery_date , organization_id , location_id , '||
'party_id , party_site_id , party_type , '||
'gst_invoice_no, gst_inv_gen_status, gst_acct_status,'||
'order_line_id, order_number ' ||
'FROM JAI_RGM_GST_INVOICE_GEN_T jrigt ' ||
'WHERE (delivery_id BETWEEN NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
'NVL('||NVL(TO_CHAR(p_delivery_id_to), 'NULL')||',delivery_id)) '||
'AND EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS wdd '||
',WSH_DELIVERY_ASSIGNMENTS wda '||
',OE_ORDER_HEADERS_ALL ooha '||
'WHERE ooha.order_number BETWEEN '||
'NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) AND '||
'NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number) '||
'AND ooha.header_id = wdd.source_header_id '||
'AND wda.delivery_detail_id = wdd.delivery_detail_id '||
'AND wda.delivery_id = jrigt.delivery_id) ' ||
'AND (TRUNC(delivery_date) BETWEEN '||
'NVL(TRUNC(TO_DATE('''||pv_delivery_date_from||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date) AND '||
'NVL(TRUNC(TO_DATE('''||pv_delivery_date_to||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date)) '||
'AND organization_id = NVL('||NVL(TO_CHAR(p_organization_id), 'NULL')||',organization_id) '||
'AND location_id = NVL('||NVL(TO_CHAR(p_location_id), 'NULL')||',location_id) ' ||
'AND (gst_inv_gen_status <> ''C'' OR /*gst_acct_status <> ''C''*/)'||
'ORDER BY party_id , party_type, party_site_id';
:= 'SELECT delivery_id , delivery_date , organization_id , location_id , '||
'party_id , party_site_id , party_type , '||
'gst_invoice_no, gst_inv_gen_status, gst_acct_status,'||
'order_line_id, order_number ' ||
'FROM JAI_RGM_GST_INVOICE_GEN_T jrigt ' ||
'WHERE (delivery_id IS NULL OR (delivery_id BETWEEN '||
'NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
'NVL('||NVL(TO_CHAR(p_delivery_id_to), 'NULL')||',delivery_id) '||
'AND EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS wdd '||
',WSH_DELIVERY_ASSIGNMENTS wda '||
',OE_ORDER_HEADERS_ALL ooha '||
'WHERE ooha.order_number BETWEEN '||
'NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) AND '||
'NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number) '||
'AND ooha.header_id = wdd.source_header_id '||
'AND wda.delivery_detail_id = wdd.delivery_detail_id '||
'AND wda.delivery_id = jrigt.delivery_id))) ' ||
'AND (order_number IS NULL '||
'OR order_number BETWEEN NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) ' ||
' AND NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number)) ' ||
'AND (TRUNC(delivery_date) BETWEEN '||
'NVL(TRUNC(TO_DATE('''||pv_delivery_date_from||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date) AND '||
'NVL(TRUNC(TO_DATE('''||pv_delivery_date_to||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date)) '||
'AND organization_id = NVL('||NVL(TO_CHAR(p_organization_id), 'NULL')||',organization_id) '||
'AND location_id = NVL('||NVL(TO_CHAR(p_location_id), 'NULL')||',location_id) '||
'AND (gst_inv_gen_status <> ''C'' /*OR gst_acct_status <> ''C''*/)'||
'ORDER BY party_id , party_type, party_site_id, order_number NULLS FIRST';
2. if successful, update GST invoice number to JAI_OM_WSH_LINES_ALL, and update
table JAI_RGM_GST_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number
, vat_inv_gen_status => 'C');
UPDATE JAI_OM_WSH_LINES_ALL
SET GST_INVOICE_NO = lv_invoice_number
, GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATE_LOGIN = fnd_global.login_id
, LAST_UPDATED_BY = fnd_global.user_id
WHERE DELIVERY_ID = mainrec.delivery_id;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET gst_invoice_no =lv_invoice_number
, gst_inv_gen_status ='C'
, gst_inv_gen_err_message = NULL
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
, last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_GST_INVOICE_GEN_T;
UPDATE JAI_OM_WSH_LINES_ALL
SET GST_INVOICE_NO = lv_invoice_number,
GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE delivery_id IN (SELECT delivery_id
FROM JAI_RGM_GST_INVOICE_GEN_T jrigt
WHERE party_id = ln_current_party_id
AND party_site_id = ln_current_party_site_id
AND party_type = mainrec.party_type
AND gst_inv_gen_status <> 'C'
AND delivery_id BETWEEN NVL(P_DELIVERY_ID_FROM,delivery_id)
AND NVL(P_DELIVERY_ID_TO,delivery_id)
AND EXISTS (SELECT 1
FROM WSH_DELIVERY_ASSIGNMENTS wda
, WSH_DELIVERY_DETAILS wdd
, OE_ORDER_HEADERS_ALL ooha
WHERE wda.delivery_id = jrigt.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_header_id = ooha.header_id
AND ooha.order_number BETWEEN
NVL(p_order_number_from, ooha.order_number) AND
NVL(p_order_number_to, ooha.order_number))
AND trunc(delivery_Date) BETWEEN NVL(P_DELIVERY_DATE_FROM,Delivery_date)
AND NVL(P_DELIVERY_DATE_TO,delivery_date));
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET gst_invoice_no = lv_invoice_number,
gst_inv_gen_status = 'C',
gst_inv_gen_err_message = NULL,
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id
WHERE delivery_id IN (SELECT delivery_id
FROM JAI_RGM_GST_INVOICE_GEN_T jrigt
WHERE party_id = ln_current_party_id
AND party_site_id = ln_current_party_site_id
AND party_type = mainrec.party_type
AND gst_inv_gen_status <> 'C'
AND delivery_id BETWEEN NVL(P_DELIVERY_ID_FROM,delivery_id)
AND NVL(P_DELIVERY_ID_TO,delivery_id)
AND EXISTS (SELECT 1
FROM WSH_DELIVERY_ASSIGNMENTS wda
, WSH_DELIVERY_DETAILS wdd
, OE_ORDER_HEADERS_ALL ooha
WHERE wda.delivery_id = jrigt.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_header_id = ooha.header_id
AND ooha.order_number BETWEEN
NVL(p_order_number_from, ooha.order_number) AND
NVL(p_order_number_to, ooha.order_number))
AND trunc(delivery_Date) BETWEEN NVL(P_DELIVERY_DATE_FROM,Delivery_date)
AND NVL(P_DELIVERY_DATE_TO,delivery_date));
Fnd_File.PUT_LINE(Fnd_File.LOG, 'No. of Deliveries updated in jai_vat_processing_t: ' || SQL%ROWCOUNT);
2. if successful, update GST invoice number to JAI_OM_WSH_LINES_ALL, and update
table JAI_RGM_GST_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
vat_inv_gen_status => 'C');
UPDATE JAI_OM_WSH_LINES_ALL
SET GST_INVOICE_NO =lv_invoice_number,
GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET gst_invoice_no = lv_invoice_number,
gst_inv_gen_status = 'C',
gst_inv_gen_err_message = NULL,
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET gst_inv_gen_status = 'E',
gst_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
2. if successful, update GST invoice number to JAI_OM_WSH_LINES_ALL, and update
table JAI_RGM_GST_INVOICE_GEN_T (vat_invoice_no => lv_invoice_number,
vat_inv_gen_status => 'C');
UPDATE JAI_OM_WSH_LINES_ALL
SET GST_INVOICE_NO = lv_invoice_number
, GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATE_LOGIN = fnd_global.login_id
, LAST_UPDATED_BY = fnd_global.user_id
WHERE order_line_id = mainrec.order_line_id
AND delivery_id IS NULL;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET gst_invoice_no = lv_invoice_number
, gst_inv_gen_status = 'C'
, gst_inv_gen_err_message = NULL
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
, last_update_date = sysdate
WHERE order_line_id = mainrec.order_line_id;
2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_GST_INVOICE_GEN_T;
UPDATE JAI_OM_WSH_LINES_ALL
SET GST_INVOICE_NO = lv_invoice_number
, GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, last_update_date = sysdate
, last_update_login = fnd_global.login_id
, last_updated_by = fnd_global.user_id
WHERE order_line_id = mainrec.order_line_id
AND delivery_id IS NULL;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET gst_invoice_no = lv_invoice_number
, gst_inv_gen_status = 'C'
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
WHERE order_line_id = mainrec.order_line_id;
2. if successful, update GST invoice number to JAI_OM_WSH_LINES_ALL, and update
table JAI_RGM_GST_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
vat_inv_gen_status => 'C');
UPDATE JAI_OM_WSH_LINES_ALL
SET GST_INVOICE_NO = lv_invoice_number
, GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATE_LOGIN = fnd_global.login_id
, LAST_UPDATED_BY = fnd_global.user_id
WHERE order_line_id = mainrec.order_line_id
AND delivery_id IS NULL;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET gst_invoice_no = lv_invoice_number
,gst_inv_gen_status = 'C'
,gst_inv_gen_err_message = NULL
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
, last_update_date = sysdate
WHERE order_line_id = mainrec.order_line_id;
2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_GST_INVOICE_GEN_T;
UPDATE JAI_OM_WSH_LINES_ALL
SET GST_INVOICE_NO = lv_invoice_number
, GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
, last_update_date = sysdate
, last_update_login = fnd_global.login_id
, last_updated_by = fnd_global.user_id
WHERE order_line_id = mainrec.order_line_id
AND delivery_id IS NULL;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET gst_invoice_no = lv_invoice_number
, gst_inv_gen_status = 'C'
, gst_inv_gen_err_message = NULL
, request_id = ln_conc_request_id
, program_id = ln_conc_progam_id
, program_application_id = ln_conc_prog_appl_id
, last_update_login = fnd_global.conc_login_id
WHERE order_line_id = mainrec.order_line_id;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET vat_acct_status = 'C',
vat_inv_gen_err_message = NULL, \*following columns added by srjayara for bug 4702156*\
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
-- WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
vat_inv_gen_status = 'E',
request_id = ln_conc_request_id, \*following columns added by srjayara for bug 4702156*\
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
-- WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_GST_INVOICE_GEN_T
SET vat_acct_err_message = substr(lv_acct_process_message,1,1000),
vat_acct_status = 'E',
request_id = ln_conc_request_id, \*following columns added by srjayara for bug 4702156*\
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
-- WHERE delivery_id = mainrec.delivery_id;
/* DELETE FROM JAI_RGM_GST_INVOICE_GEN_T
WHERE vat_inv_gen_status = 'C'
AND vat_acct_status = 'C';*/