[Home] [Help]
24:
25: 23-Nov-2010 10196549 Description: When the customer_trx_id of AR Invoice and Delivery ID
26: of ISO is same the record is not processed. customer_trx_id for AR
27: Invoice and Delivery ID for ISO Transaction are populated into
28: invoice_id column of JAI_CMN_ST_FORM_DTLS
29: Fix: Added trx_number check in cursor c_fetch_records
30:
31: 12-Jul-2012 14277151 Description: India - ST Forms Issue Processing does not pick up Invoices if they are
32: matched to a receipt with CST Taxes (PO corresponding to the Receipt does not have CST Taxes)
608: Fnd_File.put_line(Fnd_File.LOG, ' Invoice is Cancelled - not processing' );
609: end if;
610:
611:
612: delete JAI_CMN_ST_FORM_DTLS a
613: where invoice_id = c_invoices.invoice_id
614: and issue_receipt_flag = 'I'
615: and not exists (select '1'
616: from JAI_CMN_ST_MATCH_DTLS
652: Fnd_File.put_line(Fnd_File.LOG, ' Invoice is not validated - not processing' );
653: end if;
654:
655:
656: delete JAI_CMN_ST_FORM_DTLS a
657: where invoice_id = c_invoices.invoice_id
658: and issue_receipt_flag = 'I'
659: and not exists (select '1'
660: from JAI_CMN_ST_MATCH_DTLS
681: -- program should re - process all invoices as per the given input.
682: -- if invoice has already been processed then flush the st form records
683: -- for the invoice where no matching has been done.
684:
685: delete JAI_CMN_ST_FORM_DTLS a
686: where invoice_id = c_invoices.invoice_id
687: and issue_receipt_flag = 'I'
688: and not exists (select '1'
689: from JAI_CMN_ST_MATCH_DTLS
720: /* and nvl(reversal_flag, 'N') <> 'Y' */
721: /*Commented this condition for bug # 12561167 and po_distribution_id is not null*/
722: and not exists
723: (select '1'
724: from JAI_CMN_ST_FORM_DTLS
725: where invoice_id = b.invoice_id
726: and invoice_line_number = b.line_number
727: --where invoice_distribution_id = b.invoice_distribution_id
728: and issue_receipt_flag = 'I'
748: and line_type_lookup_code = lv_lt_inclu_lookup_code
749: and po_distribution_id is not null
750: and not exists
751: (select '1'
752: from JAI_CMN_ST_FORM_DTLS
753: where invoice_id = b.invoice_id
754: and invoice_line_number = b.line_number
755: and issue_receipt_flag = 'I')
756: and exists
934: end if;
935:
936: end if;
937: -- End 5763527
938: /* select JAI_CMN_ST_FORM_DTLS_S.nextval into v_st_dtl_id from dual; */
939:
940: v_rcv_transaction_id := null;
941:
942:
948: v_rcv_transaction_id := c_inv_distributions.rcv_transaction_id;
949: end if;
950:
951:
952: insert into JAI_CMN_ST_FORM_DTLS
953: (
954: st_hdr_id,
955: st_dtl_id,
956: issue_receipt_flag,
976: values
977: (
978: v_st_hdr_id,
979: --v_st_dtl_id,
980: JAI_CMN_ST_FORM_DTLS_S.nextval,
981: 'I',
982: v_po_header_id,
983: v_po_line_id,
984: v_tax_line_no,
1329: cursor c_check_duplicate(p_delivery_id Number)
1330: is
1331: select 1
1332: from JAI_CMN_STFORM_HDRS_ALL hdr ,
1333: JAI_CMN_ST_FORM_DTLS dtl
1334: where hdr.st_hdr_id = dtl.st_hdr_id
1335: and dtl.invoice_id = p_delivery_id
1336: and hdr.party_type_flag = 'C';
1337:
1367: AND jtc.stform_type IS NOT NULL
1368: )
1369: AND NOT EXISTS
1370: (SELECT 1
1371: FROM JAI_CMN_ST_FORM_DTLS jstd
1372: WHERE jstd.header_id = oeh.header_id
1373: AND jstd.invoice_id = wnd.delivery_id
1374: AND jstd.line_id = jspl.order_line_id
1375: AND jstd.doc_type = 'ISO' /*Bug 10196549 - Clause required to distinguish transactions with same customer_trx_id and delivery_id*/
1507: when processed for ISO orders post shipment.
1508: This procedure will be called from the wrapper program - jai_cmn_st_forms_pkg.generate_forms
1509: when party_type parameter = 'C'
1510:
1511: Doc_Type field in the JAI_CMN_ST_FORM_DTLS is set to 'ISO'
1512:
1513: Grouping in the form will be done based on excise invoice number, where excise invoice number is
1514: generated for the iso order. Delivery id in is used for this grouping, in case excise invoice
1515: number is not generated.
1547: If the control comes here , it is assumed that the delivery which is being looped has st forms
1548: related taxes no check done here again.
1549: need to insert records into the st forms hdr and st forms detail tables.
1550: JAI_CMN_STFORM_HDRS_ALL
1551: JAI_CMN_ST_FORM_DTLS
1552: */
1553: fnd_file.put_line(FND_FILE.LOG,'1.1 Delivery : ' || st_forms_rec.delivery_id || ' not present in St form tables, hence processing.' );
1554:
1555: FOR tax_rec IN c_get_taxes(st_forms_rec.delivery_id)
1638: END IF;
1639:
1640:
1641:
1642: INSERT INTO JAI_CMN_ST_FORM_DTLS(
1643: ST_HDR_ID ,
1644: ST_DTL_ID ,
1645: HEADER_ID , -- order header id
1646: LINE_ID , -- order line id
1663: LAST_UPDATED_BY ,
1664: LAST_UPDATE_LOGIN
1665: ) values (
1666: v_st_hdr_id ,
1667: JAI_CMN_ST_FORM_DTLS_S.nextval ,
1668: v_order_hdr_id ,
1669: v_order_line_id ,
1670: tax_rec.tax_id ,
1671: tax_rec.tax_line_no ,
1687: fnd_global.user_id ,
1688: fnd_global.login_id
1689:
1690: ) RETURNING st_dtl_id INTO v_st_line_id;
1691: fnd_file.put_line(FND_FILE.LOG,' inserting into JAI_CMN_ST_FORM_DTLS table with detail id :' ||v_st_line_id );
1692: v_base_tax_amt :=NULL;
1693:
1694: EXCEPTION
1695: WHEN OTHERS THEN
1781: cursor c_check_duplicate(p_invoice_id Number, p_trx_number varchar2) /*Added trx_number parameter for bug # 9113108 */
1782: is
1783: select 1
1784: from JAI_CMN_STFORM_HDRS_ALL hdr ,
1785: JAI_CMN_ST_FORM_DTLS dtl
1786: where hdr.st_hdr_id = dtl.st_hdr_id
1787: and dtl.invoice_id = p_invoice_id
1788: and dtl.TRX_NUMBER = p_trx_number /*Added trx_number parameter for bug # 9113108 */
1789: and hdr.party_type_flag = 'C';
1806: trx.ship_to_site_use_id = nvl(p_party_site_id,trx.ship_to_site_use_id)) AND
1807: trx.org_id = nvl(p_org_id,trx.org_id) AND
1808: NOT EXISTS
1809: (SELECT 1
1810: FROM JAI_CMN_ST_FORM_DTLS a ,
1811: JAI_CMN_STFORM_HDRS_ALL b
1812: WHERE b.party_id = nvl(p_party_id,b.party_id) AND
1813: b.party_site_id = nvl(p_party_site_id,b.party_site_id) AND
1814: b.party_type_flag = 'C' AND
2036: If the control comes here , it is assumed that the invoice which is being looped has st forms related taxes
2037: no check done here again.
2038: need to insert records into the st forms hdr and st forms detail tables.
2039: JAI_CMN_STFORM_HDRS_ALL
2040: JAI_CMN_ST_FORM_DTLS
2041: */
2042: 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.' );
2043:
2044: FOR tax_rec IN c_get_taxes(st_forms_rec.customer_trx_id)
2151: fetch c_orgn_locn into v_orgn_id , v_locn_id;
2152: close c_orgn_locn;
2153: end if;
2154:
2155: fnd_file.put_line(FND_FILE.LOG,'before inserting into JAI_CMN_ST_FORM_DTLS table');
2156:
2157: INSERT INTO JAI_CMN_ST_FORM_DTLS(
2158: ST_HDR_ID ,
2159: ST_DTL_ID ,
2153: end if;
2154:
2155: fnd_file.put_line(FND_FILE.LOG,'before inserting into JAI_CMN_ST_FORM_DTLS table');
2156:
2157: INSERT INTO JAI_CMN_ST_FORM_DTLS(
2158: ST_HDR_ID ,
2159: ST_DTL_ID ,
2160: HEADER_ID , -- order header id
2161: LINE_ID , -- order line id
2177: LAST_UPDATED_BY ,
2178: LAST_UPDATE_LOGIN
2179: ) values (
2180: v_st_hdr_id ,
2181: JAI_CMN_ST_FORM_DTLS_S.nextval ,
2182: v_order_hdr_id ,
2183: v_order_line_id ,
2184: tax_rec.tax_id ,
2185: tax_rec.tax_line_no ,