[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select tax_id, line_location_id, po_header_id, po_line_id, tax_amount,
parent_invoice_line_number --parent_invoice_distribution_id /* uptake of ap_invoice_lines_all */
,recoverable_flag -- 5763527
from JAI_AP_MATCH_INV_TAXES
where invoice_id = p_invoice_id
and invoice_line_number = p_line_number; /* uptake of ap_invoice_lines_all */
select tax_type, tax_rate, stform_type
, mod_cr_percentage -- 5763527
from JAI_CMN_TAXES_ALL
where tax_id = p_tax_id;
select vendor_id, vendor_site_id, org_id
from ap_invoices_all
where invoice_id = p_invoice_id;
select st_hdr_id
from JAI_CMN_STFORM_HDRS_ALL
where party_type_flag = 'V'
and party_id = p_vendor_id
and party_site_id = p_vendor_site_id
and form_type = p_stform_type
and org_id = p_org_id;
select segment1, type_lookup_code
from po_headers_all
where po_header_id = p_po_header_id;
select line_focus_id
from JAI_PO_LINE_LOCATIONS
where line_location_id = p_line_location_id
and po_line_id = p_line_id;
select tax_line_no
from JAI_RCV_LINE_TAXES
where (shipment_header_id, shipment_line_id)
in
(select shipment_header_id, shipment_line_id
from rcv_transactions
where transaction_id = p_rcv_transaction_id
)
and tax_id = p_tax_id;
select tax_line_no
from JAI_PO_TAXES
where tax_id = p_tax_id
and line_location_id = p_po_line_location_id;
select match_option, ship_to_organization_id, ship_to_location_id
from po_line_locations_all
where line_location_id = p_po_line_location_id;
select rcv_transaction_id
from ap_invoice_lines_all --ap_invoice_distributions_all /* uptake of ap_invoice_lines_all */
where invoice_id = p_invoice_id
and line_type_lookup_code = cp_lt_lookup_code --'ITEM'
and po_distribution_id = p_po_distribution_id;
/*select count(1)
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and nvl(match_status_flag, 'N') <> 'A';*/
SELECT 'Y'
FROM dual
WHERE exists (select '1'
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and nvl(match_status_flag, 'N') <> 'A');
select set_of_books_id, invoice_currency_code,exchange_date, exchange_rate_type, exchange_rate
from ap_invoices_all
where invoice_id = p_invoice_id;
select line_number, po_distribution_id, rcv_transaction_id, amount, match_type /* invoice_distribution_id*/
from ap_invoice_lines_all --ap_invoice_distributions_all
where invoice_id = p_invoice_id
and line_number = p_line_number ;
select po_header_id, po_line_id, line_location_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id;
select distinct po_header_id, po_line_id, line_location_id
from jai_ap_match_inv_taxes
where invoice_id = p_invoice_id_match
and parent_invoice_line_number = v_line_number;
select precedence_1, precedence_2 , precedence_3, precedence_4, precedence_5
from JAI_PO_TAXES
where tax_id = p_tax_id
and line_location_id = p_po_line_location_id;
SELECT precedence_1, precedence_2, precedence_3, precedence_4, precedence_5
FROM jai_rcv_line_taxes
WHERE shipment_line_id IN (SELECT shipment_line_id
FROM RCV_TRANSACTIONS
WHERE transaction_id = p_rcv_transaction_id)
AND tax_id = p_tax_id;
select amount
from ap_invoice_lines_all
where invoice_id = p_invoice_id
and line_number =
( select jmit.invoice_line_number
from JAI_AP_MATCH_INV_TAXES jmit
where jmit.invoice_id = p_invoice_id
and jmit.po_header_id = p_po_header_id
and jmit.po_line_id = p_po_line_id
and jmit.line_location_id = p_line_location_id
and nvl(jmit.po_distribution_id,1) = nvl(p_po_distribution_id,nvl(jmit.po_distribution_id,1)) /*Added by nprashar for bug # 12561167 */
and jmit.parent_invoice_line_number = p_parent_line_number
and jmit.tax_id = (select jpt.tax_id from JAI_PO_TAXES jpt
, jai_cmn_taxes_all jcta -- for bug 14183827 by anupgupt
Where jpt.line_location_id = p_line_location_id
and jpt.tax_line_no = p_precedence
and jpt.tax_id = jcta.tax_id -- for bug 14183827 by anupgupt
and nvl(jcta.inclusive_tax_flag,'N') <> 'Y' -- for bug 14183827 by anupgupt
and jpt.tax_id = jmit.tax_id));
select tax_id
from JAI_PO_TAXES
where line_location_id = p_line_location_id
and tax_line_no in
(p_precedence_1, p_precedence_2, p_precedence_3, p_precedence_4, p_precedence_5)
)
);*/
select SUM(base_amount)
from jai_ap_match_inv_taxes
where invoice_id = c_invoice_id
and parent_invoice_distribution_id = c_invoice_distribution_id
and po_header_id = c_po_header_id
and po_line_id = c_po_line_id
and line_location_id = c_line_location_id
and nvl(po_distribution_id,1) = nvl(c_po_distribution_id,nvl(po_distribution_id,1)); /*Added NVL condition for bug # 12561167 */
select jamit.tax_amount into v_tax_base_amt_loop
from JAI_AP_MATCH_INV_TAXES jamit
, jai_cmn_taxes_all jcta -- for bug 14183827 by anupgupt
Where jamit.invoice_id = p_invoice_id
and jamit.po_header_id = v_po_header_id
and jamit.po_line_id = v_po_line_id
and jamit.line_location_id = v_po_line_location_id
and nvl(jamit.po_distribution_id,1) = nvl(v_po_distribution_id,nvl(jamit.po_distribution_id,1)) /*Added by nprashar for bug # 12561167 */
and jamit.parent_invoice_line_number = v_line_number
and jamit.line_no = p_prec_check(p_value_check)
and jamit.tax_id = jcta.tax_id -- for bug 14183827 by anupgupt
and nvl(jcta.inclusive_tax_flag,'N') <> 'Y'; -- for bug 14183827 by anupgupt
the line is deleted. Similar processing is also done for an invoice that is not validated.
If the option of re-process is given as Yes, every invoice is checked for already
processed line that is not issued and is deleted and processed again.
The distribution lines which are reversed are not considered. Only the lines processed
by localization are considered for this processing.
2. 11-Nov-2003 Aiyer - Bug #3249375 File Version 617.1
Changed the variable declaration from reference to ja_in_po_st_forms_hdr.form_issue_id to JAI_CMN_STFORM_HDRS_ALL.st_hdr_id.
This was required as the table ja_in_po_st_forms_hdr has been obsoleted.
As this table does not exist in the database any more, post application of IN60105D1 patchset hence deleting
the reference .
Dependency Due to This Bug:-
Can be applied only post application of IN60105D1.
3. 29-JULY-2008 JMEENA for bug#7214273
Added NVL with variable v_not_validated_count and reset to N before processing.
4. 19-Nov-2008 Changes by nprashar for bug # 6043559, FP changes of bug 5999535.
Changes done in procedure - process_ar_st_forms.process_ap_st_forms
Here added a new cursor - c_receipt_base_amt and added the code to open/fetch/close the same
5. 15-Jul-2010 Changes by Jia for bug#9535954
Issue: the ST form should not consider the inclusive tax
Fixed: Changes done in procedure - generate_ap_forms
modified the cursor - c_inv_distributions
Future Dependencies For the release Of this Object:-
==================================================
(Please add a row in the section below only if your bug introduces a dependency due to
spec change/A new call to a object/A datamodel change)
----------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
jai_cmn_st_forms_pkg.generate_ap_forms
----------------------------------------------------------------------------------------------------------------------------------------------------
617.1 3249375 IN60105D1 Aiyer 11/Nov/2003 Can be applied only after IN60105D1 patchset
has been applied.
------------------------------------------------------------------------------------------------------- */
v_debug := jai_constants.yes ; --Ramananda for File.Sql.35
select invoice_id, invoice_num, cancelled_date
from ap_invoices_all a
where invoice_date between trunc(p_invoice_from_date) and trunc(p_invoice_to_date)
and ( (p_org_id is null) or (p_org_id is not null and org_id = p_org_id) )
and ( (p_vendor_id is null) or (p_vendor_id is not null and vendor_id = p_vendor_id) )
and ( (p_vendor_site_id is null)
or
(p_vendor_site_id is not null and vendor_site_id = p_vendor_site_id)
)
and exists (select '1'
from JAI_AP_MATCH_INV_TAXES
where invoice_id = a.invoice_id
and tax_id in (select tax_id from JAI_CMN_TAXES_ALL where stform_type is not null)
) -- to ensure that loc taxes exists for the invoice and are of st forms type.
order by invoice_date asc
)
loop
begin
if v_debug = 'Y' then
Fnd_File.put_line(Fnd_File.LOG, ' ** Processing invoice (id) : '
|| c_invoices.invoice_num || '('
|| c_invoices.invoice_id || '}' );
delete JAI_CMN_ST_FORM_DTLS a
where invoice_id = c_invoices.invoice_id
and issue_receipt_flag = 'I'
and not exists (select '1'
from JAI_CMN_ST_MATCH_DTLS
where st_hdr_id = a.st_hdr_id
and st_dtl_id = a.st_dtl_id
);
delete JAI_CMN_ST_FORM_DTLS a
where invoice_id = c_invoices.invoice_id
and issue_receipt_flag = 'I'
and not exists (select '1'
from JAI_CMN_ST_MATCH_DTLS
where st_hdr_id = a.st_hdr_id
and st_dtl_id = a.st_dtl_id
);
' No of unmatched records deleted from st forms for this invoice :'
|| to_char(sql%rowcount) );
delete JAI_CMN_ST_FORM_DTLS a
where invoice_id = c_invoices.invoice_id
and issue_receipt_flag = 'I'
and not exists (select '1'
from JAI_CMN_ST_MATCH_DTLS
where st_hdr_id = a.st_hdr_id
and st_dtl_id = a.st_dtl_id
);
select invoice_id,
line_number, --distribution_line_number
--invoice_distribution_id,
po_distribution_id,
rcv_transaction_id
from ap_invoice_lines_all b --ap_invoice_distributions_all b
where invoice_id = c_invoices.invoice_id
and line_type_lookup_code = lv_lt_lookup_code --'MISCELLANEOUS' -- only tax lines /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
/* and nvl(reversal_flag, 'N') <> 'Y' */
/*Commented this condition for bug # 12561167 and po_distribution_id is not null*/
and not exists
(select '1'
from JAI_CMN_ST_FORM_DTLS
where invoice_id = b.invoice_id
and invoice_line_number = b.line_number
--where invoice_distribution_id = b.invoice_distribution_id
and issue_receipt_flag = 'I'
) -- to ensure that the line is not processed twice.
-- This should not happen as the invoice is already checked for prior processing.
and exists
(select '1'
from JAI_AP_MATCH_INV_TAXES
where invoice_id = b.invoice_id
--and distribution_line_number = b.distribution_line_number
and invoice_line_number = b.line_number
and tax_id in (select tax_id from JAI_CMN_TAXES_ALL where stform_type is not null)
) -- to ensure that the line is an india local tax line for a st form type tax
-- Added by Jia for Bug#9535954, Begin
------------------------------------------------------------------------------------------------------
Union All
select invoice_id,
line_number,
po_distribution_id,
rcv_transaction_id
from ap_invoice_lines_all b
where invoice_id = c_invoices.invoice_id
and line_type_lookup_code = lv_lt_inclu_lookup_code
and po_distribution_id is not null
and not exists
(select '1'
from JAI_CMN_ST_FORM_DTLS
where invoice_id = b.invoice_id
and invoice_line_number = b.line_number
and issue_receipt_flag = 'I')
and exists
(select '1'
from JAI_AP_MATCH_INV_TAXES
where invoice_id = b.invoice_id
and invoice_line_number = b.line_number
and tax_id in
(select tax_id
from JAI_CMN_TAXES_ALL
where stform_type is not null
and inclusive_tax_flag = 'Y'))
------------------------------------------------------------------------------------------------------
-- Added by Jia for Bug#9535954, End
order by line_number --distribution_line_number
)
loop
v_invoice_error_flag := 'N';
insert into JAI_CMN_STFORM_HDRS_ALL
(
st_hdr_id,
party_id,
party_site_id,
form_type,
org_id,
party_type_flag,
creation_date,
created_by,
last_update_date,
last_updated_by
)
values
(
--v_st_hdr_id,
JAI_CMN_STFORM_HDRS_ALL_S.nextval,
v_vendor_id,
v_vendor_site_id,
v_stform_type,
v_org_id,
'V',
sysdate,
v_uid,
sysdate,
v_uid
) returning st_hdr_id into v_st_hdr_id ;
/* select JAI_CMN_ST_FORM_DTLS_S.nextval into v_st_dtl_id from dual; */
insert into JAI_CMN_ST_FORM_DTLS
(
st_hdr_id,
st_dtl_id,
issue_receipt_flag,
header_id,
line_id,
tax_line_no,
tax_id,
po_num,
doc_type,
tax_target_amount,
po_line_location_id,
rcv_transaction_id,
invoice_id,
invoice_line_number, --invoice_distribution_id,
organization_id,
location_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
values
(
v_st_hdr_id,
--v_st_dtl_id,
JAI_CMN_ST_FORM_DTLS_S.nextval,
'I',
v_po_header_id,
v_po_line_id,
v_tax_line_no,
v_tax_id,
v_po_num,
v_doc_type,
v_tax_target_amount,
v_po_line_location_id,
v_rcv_transaction_id, -- c_inv_distributions.rcv_transaction_id,
c_inv_distributions.invoice_id,
c_inv_distributions.line_number, --c_inv_distributions.invoice_distribution_id,
v_ship_to_organization_id,
v_ship_to_location_id,
sysdate,
v_uid,
sysdate,
v_uid,
v_uid
) returning st_dtl_id into v_st_dtl_id;
SELECT concurrent_program_id, nvl(enable_trace,'N')
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = p_request_id;
SELECT a.sid, a.serial#, b.spid FROM v$session a,v$process b
WHERE audsid = userenv('SESSIONID')
AND a.paddr = b.addr;
SELECT name FROM v$database;
select st_hdr_id
from JAI_CMN_STFORM_HDRS_ALL
where party_id = p_party_id
and party_site_id = p_party_site_id
and form_type = p_form_type
and org_id = p_org_id
and party_type_flag = 'C';
select order_line_id ,
order_header_id ,
organization_id ,
location_id
from JAI_OM_WSH_LINES_ALL
where delivery_detail_id = p_delivery_detail_id;
select order_number
from oe_order_headers_all
where header_id = p_order_header_id;
select 1
from JAI_CMN_STFORM_HDRS_ALL hdr ,
JAI_CMN_ST_FORM_DTLS dtl
where hdr.st_hdr_id = dtl.st_hdr_id
and dtl.invoice_id = p_delivery_id
and hdr.party_type_flag = 'C';
SELECT wnd.delivery_id ,
wdd.org_id ,
wdd.source_header_number ,
wdd.source_header_type_id ,
wdd.source_header_type_name ,
oeh.sold_to_org_id customer_id ,
oeh.ship_to_org_id customer_site_id,
jspl.excise_invoice_no
FROM wsh_new_deliveries wnd ,
wsh_delivery_details wdd ,
JAI_OM_WSH_LINES_ALL jspl ,
oe_order_headers_all oeh
WHERE jspl.delivery_id = wnd.delivery_id
AND wdd.delivery_Detail_id = jspl.delivery_detail_id
AND wdd.source_header_id = oeh.header_id
AND oeh.source_document_type_id = 10
AND wdd.org_id = nvl(p_org_id, wdd.org_id) /*Added nvl clause by mmurtuza for bug 16521623 */
AND oeh.sold_to_org_id = nvl(p_party_id,oeh.sold_to_org_id)
AND oeh.ship_to_org_id = nvl(p_party_site_id,oeh.ship_to_org_id)
AND trunc(jspl.creation_date) between p_from_date and p_to_date
AND EXISTS
(SELECT 1
FROM JAI_OM_WSH_LINE_TAXES jsptl ,
JAI_CMN_TAXES_ALL jtc
WHERE jtc.tax_id = jsptl.tax_id
AND jsptl.delivery_detail_id = jspl.delivery_detail_id
AND jtc.tax_type IN ( jai_constants.tax_type_sales, jai_constants.tax_type_cst) --('Sales Tax','CST') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND jtc.stform_type IS NOT NULL
)
AND NOT EXISTS
(SELECT 1
FROM JAI_CMN_ST_FORM_DTLS jstd
WHERE jstd.header_id = oeh.header_id
AND jstd.invoice_id = wnd.delivery_id
AND jstd.line_id = jspl.order_line_id
AND jstd.doc_type = 'ISO' /*Bug 10196549 - Clause required to distinguish transactions with same customer_trx_id and delivery_id*/
AND jstd.order_flag = 'O'
)
GROUP BY wnd.delivery_id ,
wdd.org_id ,
wdd.source_header_number ,
wdd.source_header_type_id ,
wdd.source_header_type_name ,
oeh.sold_to_org_id ,
oeh.ship_to_org_id,
jspl.excise_invoice_no;
select
jsptl.TAX_LINE_NO ,
jsptl.delivery_detail_ID ,
jsptl.PRECEDENCE_1 ,
jsptl.PRECEDENCE_2 ,
jsptl.PRECEDENCE_3 ,
jsptl.PRECEDENCE_4 ,
jsptl.PRECEDENCE_5 ,
jsptl.TAX_ID ,
jsptl.TAX_RATE ,
jsptl.QTY_RATE ,
jsptl.UOM ,
jsptl.TAX_AMOUNT ,
jsptl.base_tax_amount ,
jtc.stform_type
from JAI_OM_WSH_LINE_TAXES jsptl ,
JAI_CMN_TAXES_ALL jtc
where delivery_detail_id in
(
select delivery_detail_id
from JAI_OM_WSH_LINES_ALL
where delivery_id = p_delivery_id
)
and jtc.tax_id = jsptl.tax_id
and jtc.tax_type in ( jai_constants.tax_type_sales, jai_constants.tax_type_cst) --('Sales Tax','CST') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
and jtc.stform_type is not null;
SELECT SUM(tax_amount)
FROM JAI_OM_WSH_LINE_TAXES
WHERE delivery_detail_id = p_delivery_Detail_id
AND tax_line_no IN (p_p1,p_p2,p_p3,p_p4,p_p5);
select precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5
from JAI_OM_WSH_LINE_TAXES
where delivery_detail_id = p_delivery_Detail_id
and tax_id = p_tax_id;
select selling_price * quantity line_amount
from JAI_OM_WSH_LINES_ALL
where delivery_detail_id = p_delivery_Detail_id;
need to insert records into the st forms hdr and st forms detail tables.
JAI_CMN_STFORM_HDRS_ALL
JAI_CMN_ST_FORM_DTLS
*/
fnd_file.put_line(FND_FILE.LOG,'1.1 Delivery : ' || st_forms_rec.delivery_id || ' not present in St form tables, hence processing.' );
INSERT INTO JAI_CMN_STFORM_HDRS_ALL(
st_hdr_id ,
party_id ,
party_site_id ,
form_type ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
org_id ,
party_type_flag
) values (
JAI_CMN_STFORM_HDRS_ALL_S.nextval ,
st_forms_rec.customer_id ,
st_forms_rec.customer_site_id ,
tax_rec.stform_type ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
st_forms_rec.org_id ,
'C'
) RETURNING st_hdr_id INTO v_st_hdr_id;
fnd_file.put_line(FND_FILE.LOG,' inserting into JAI_CMN_STFORM_HDRS_ALL table with header id '|| v_st_hdr_id);
INSERT INTO JAI_CMN_ST_FORM_DTLS(
ST_HDR_ID ,
ST_DTL_ID ,
HEADER_ID , -- order header id
LINE_ID , -- order line id
TAX_ID ,
TAX_LINE_NO ,
INVOICE_ID , -- delivery id
ISSUE_RECEIPT_FLAG ,
TAX_TARGET_AMOUNT ,
MATCHED_AMOUNT ,
ORDER_FLAG , -- 'O'
ORDER_NUMBER , -- sales order number
TRX_TYPE_ID ,
TRX_NUMBER , -- excise invoice number, if null then delivery
organization_id ,
location_id ,
doc_type , -- Hard coded Value 'ISO' passed
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
) values (
v_st_hdr_id ,
JAI_CMN_ST_FORM_DTLS_S.nextval ,
v_order_hdr_id ,
v_order_line_id ,
tax_rec.tax_id ,
tax_rec.tax_line_no ,
st_forms_rec.delivery_id ,
'R' ,
nvl(tax_rec.base_tax_amount
,v_base_tax_amt) ,
NULL , -- matched amount
v_order_flag ,
v_order_num ,
st_forms_rec.source_header_type_id ,
nvl(st_forms_rec.excise_invoice_no, st_forms_rec.delivery_id) , -- need to confirm it after discussion
v_orgn_id ,
v_locn_id ,
'ISO' ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
) RETURNING st_dtl_id INTO v_st_line_id;
fnd_file.put_line(FND_FILE.LOG,' inserting into JAI_CMN_ST_FORM_DTLS table with detail id :' ||v_st_line_id );
select st_hdr_id
from JAI_CMN_STFORM_HDRS_ALL
where party_id = p_party_id
and party_site_id = p_party_site_id
and form_type = p_form_type
and org_id = p_org_id
and party_type_flag = 'C';
select interface_line_attribute6
from ra_customer_trx_lines_all
where customer_Trx_line_id = p_customer_trx_line_id;
select header_id
from oe_order_lines_all
where line_id = p_order_line_id;
select order_number
from oe_order_headers_all
where header_id = p_order_header_id;
select 1
from JAI_CMN_STFORM_HDRS_ALL hdr ,
JAI_CMN_ST_FORM_DTLS dtl
where hdr.st_hdr_id = dtl.st_hdr_id
and dtl.invoice_id = p_invoice_id
and dtl.TRX_NUMBER = p_trx_number /*Added trx_number parameter for bug # 9113108 */
and hdr.party_type_flag = 'C';
select trx.customer_trx_id , trx.org_id , trx.trx_number , trx.cust_trx_type_id , trx.created_from,
nvl(trx.bill_to_customer_id,trx.ship_to_customer_id) customer_id, nvl(trx.bill_to_site_use_id,trx.ship_to_site_use_id) customer_site_id,
decode( trx_types.TYPE ,'INV','Invoice','CM','Credit Memo','DM','Debit Memo',trx_types.TYPE ) document_type /*JMEENA for bug#4932256( FP 4913641)*/
from ra_customer_Trx_all trx ,
ra_cust_trx_types_all trx_types ,
jai_ar_trxs jtrx /* Added for bug#5376622 */
where
trx.customer_trx_id = jtrx.customer_trx_id AND
(trx.bill_to_customer_id = nvl(p_party_id,trx.bill_to_customer_id)
OR
trx.ship_to_customer_id = nvl(p_party_id,trx.ship_to_customer_id)) AND
(trx.bill_to_site_use_id = nvl(p_party_site_id,trx.bill_to_site_use_id)
OR
trx.ship_to_site_use_id = nvl(p_party_site_id,trx.ship_to_site_use_id)) AND
trx.org_id = nvl(p_org_id,trx.org_id) AND
NOT EXISTS
(SELECT 1
FROM JAI_CMN_ST_FORM_DTLS a ,
JAI_CMN_STFORM_HDRS_ALL b
WHERE b.party_id = nvl(p_party_id,b.party_id) AND
b.party_site_id = nvl(p_party_site_id,b.party_site_id) AND
b.party_type_flag = 'C' AND
a.st_hdr_id = b.st_hdr_id AND
a.invoice_id = trx.customer_trx_id AND
a.trx_number = trx.trx_number /*Bug 10196549 - Clause required to distinguish transactions with same customer_trx_id and delivery_id*/
) AND
EXISTS
(SELECT 1
FROM JAI_AR_TRX_LINES trx_lines
WHERE customer_trx_id = trx.customer_trx_id AND
EXISTS
(SELECT 1
FROM JAI_AR_TRX_TAX_LINES tax_lines ,
JAI_CMN_TAXES_ALL jtc
WHERE link_to_cust_trx_line_id = trx_lines.customer_trx_line_id AND
tax_type IN ( jai_constants.tax_type_sales, jai_constants.tax_type_cst) --('Sales Tax','CST') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND jtc.tax_id = tax_lines.tax_id AND
jtc.stform_type IS NOT NULL
)
and trx.complete_flag ='Y'
AND trx_date between p_from_date and p_to_date
and trx_types.type in ('INV' ,'CM','DM')/*JMEENA for bug#4932256 (FP 4913641) . Added CM and DM*/
and trx_types.cust_Trx_type_id = trx.cust_trx_type_id
and trx_types.org_id = trx.org_id
);
select
jtxn.TAX_LINE_NO ,
jtxn.CUSTOMER_TRX_LINE_ID ,
jtxn.LINK_TO_CUST_TRX_LINE_ID ,
jtxn.PRECEDENCE_1 ,
jtxn.PRECEDENCE_2 ,
jtxn.PRECEDENCE_3 ,
jtxn.PRECEDENCE_4 ,
jtxn.PRECEDENCE_5 ,
jtxn.TAX_ID ,
jtxn.TAX_RATE ,
jtxn.QTY_RATE ,
jtxn.UOM ,
jtxn.TAX_AMOUNT ,
jtxn.INVOICE_CLASS ,
jtxn.base_tax_amount ,
jtc.stform_type
from JAI_AR_TRX_TAX_LINES jtxn ,
JAI_CMN_TAXES_ALL jtc
where link_to_cust_Trx_line_id in
(
select customer_Trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_id = p_invoice_id
)
and jtc.tax_id = jtxn.tax_id
and jtc.tax_type in ( jai_constants.tax_type_sales, jai_constants.tax_type_cst) --('Sales Tax','CST') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
and jtc.stform_type is not null;
select organization_id , location_id
from JAI_AR_TRXS
where customer_trx_id = p_invoice_id;
select type
from ra_cust_trx_types_all
where cust_trx_type_id = p_cust_trx_type_id;
SELECT SUM(tax_amount)
FROM JAI_AR_TRX_TAX_LINES
WHERE link_to_cust_trx_line_id = p_Link_to_line_id
AND tax_line_no IN
(
SELECT precedence_1
FROM JAI_AR_TRX_TAX_LINES
WHERE link_to_cust_trx_line_id = p_Link_to_line_id
and precedence_1 is not null
AND tax_id = p_tax_id
UNION
SELECT precedence_2
FROM JAI_AR_TRX_TAX_LINES
WHERE link_to_cust_trx_line_id = p_Link_to_line_id
AND tax_id = p_tax_id
and precedence_1 is not null
UNION
SELECT precedence_3
FROM JAI_AR_TRX_TAX_LINES
WHERE link_to_cust_trx_line_id = p_Link_to_line_id
AND tax_id = p_tax_id
and precedence_1 is not null
UNION
SELECT precedence_4
FROM JAI_AR_TRX_TAX_LINES
WHERE link_to_cust_trx_line_id = p_Link_to_line_id
and precedence_1 is not null
AND tax_id = p_tax_id
UNION
SELECT precedence_5
FROM JAI_AR_TRX_TAX_LINES
WHERE link_to_cust_trx_line_id = p_Link_to_line_id
and precedence_1 is not null
AND tax_id = p_tax_id
);
select precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id = p_Link_to_line_id
and tax_id = p_tax_id;
is select line_amount
from JAI_AR_TRX_LINES
where customer_trx_line_id = p_link_to_line_id;
v_hdr_record_exists variable which is used to check whether header is inserted or not is retaining its value
in the loop. This is fixed by assigning NULL at the start of the loop so that it wont retain its value.
Also the code SELECT .nextval INTO FROM DUAL is modified to populate during INSERT
Statement. Usage of v_success variable is removed
3. 18/11/2008 JMEENA for bug#4932256 (FP 4913641)
Issue: Debit Notes and Credit Notes are not considered for AR ST Forms tracking in AR
Fix: Modified the cursor c_fetch_records to fetch CM and DM also
-----------------------------------------------------------------------------------------------------------------*/
v_some_errors := '0'; -- used to identify if all was successful --Ramananda for File.Sql.35
need to insert records into the st forms hdr and st forms detail tables.
JAI_CMN_STFORM_HDRS_ALL
JAI_CMN_ST_FORM_DTLS
*/
fnd_file.put_line(FND_FILE.LOG,'1.1 Invoice : '||st_forms_rec.document_type||':' || st_forms_rec.customer_trx_id || ' not present in St form tables, hence processing.' );
fnd_file.put_line(FND_FILE.LOG,'before inserting into JAI_CMN_STFORM_HDRS_ALL table');
INSERT INTO JAI_CMN_STFORM_HDRS_ALL(
st_hdr_id ,
party_id ,
party_site_id ,
form_type ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
org_id ,
party_type_flag
) values (
JAI_CMN_STFORM_HDRS_ALL_S.nextval ,
st_forms_rec.customer_id ,
st_forms_rec.customer_site_id ,
tax_rec.stform_type ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
st_forms_rec.org_id ,
'C'
) RETURNING st_hdr_id INTO v_st_hdr_id;
fnd_file.put_line(FND_FILE.LOG,'before inserting into JAI_CMN_ST_FORM_DTLS table');
INSERT INTO JAI_CMN_ST_FORM_DTLS(
ST_HDR_ID ,
ST_DTL_ID ,
HEADER_ID , -- order header id
LINE_ID , -- order line id
TAX_ID ,
TAX_LINE_NO ,
INVOICE_ID , -- customer trx id
ISSUE_RECEIPT_FLAG ,
TAX_TARGET_AMOUNT ,
MATCHED_AMOUNT ,
ORDER_FLAG , -- 'O'
ORDER_NUMBER , -- sales order number
TRX_TYPE_ID ,
TRX_NUMBER , -- invoice num
organization_id ,
location_id ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
) values (
v_st_hdr_id ,
JAI_CMN_ST_FORM_DTLS_S.nextval ,
v_order_hdr_id ,
v_order_line_id ,
tax_rec.tax_id ,
tax_rec.tax_line_no ,
st_forms_rec.customer_Trx_id ,
'R' ,
nvl(tax_rec.base_tax_amount
,v_base_tax_amt) ,
NULL , -- matched amount
v_order_flag ,
v_order_num ,
st_forms_rec.cust_trx_type_id ,
st_forms_rec.trx_number ,
v_orgn_id ,
v_locn_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
) RETURNING st_dtl_id INTO v_st_line_id;