[Home] [Help]
17: g_doc_type gms_bc_packets.document_type%type;
18: g_non_gms_txn BOOLEAN;
19: g_bc_packet_has_P82_records Varchar2(1); -- Used in handle_net_zero_txn/update_fc_sequence
20: g_ip_fc_flag VARCHAR2(1); -- If FC called for IP or not ..
21: g_gl_bc_pkt_sponsored_count NUMBER; -- Count of sponsored transactions in gl_bc_packets(AP/PO/REQ)
22: g_pa_addition_flag_t_count NUMBER; -- Count of sponsored AP records with pa_addition_flag = 'T'
23:
24: -- Funds Check Return Code for the Packet processed.
25: g_return_code gms_bc_packets.result_code%TYPE;
1301: -- Duplicate Distribution lines in REQ/PO
1302: -- Match a PO with Invoice
1303: ----------------------------------------------------------------------------------------------------------
1304:
1305: -- R12 Funds management uptake : With R12 this API will get fired before inserting records into gl_bc_packets,
1306: -- hence modified the whole logic of this procedure .
1307: -- Logic is now based on global PLSQL variables populated by copy_gl_pkt_to_gms_pkt procedure
1308:
1309: ----------------------------------------------------------------------------------------------------------
1685: END misc_synch_adls;
1686:
1687: -- R12 Funds Management Uptake : Obsolete data_transfer_failure procedure as this logic is handled in copy_gl_pkt_to_gms_pkt
1688: --============================================================================================
1689: -- Bug 2899151 : This procedure will fail gl_bc_packets and gms_bc_packets
1690: -- This procedure will be used when all records in gl_bc_packets (corresponding to sponsored)
1691: -- and not existing in gms_bc_packets.
1692: --============================================================================================
1693:
1686:
1687: -- R12 Funds Management Uptake : Obsolete data_transfer_failure procedure as this logic is handled in copy_gl_pkt_to_gms_pkt
1688: --============================================================================================
1689: -- Bug 2899151 : This procedure will fail gl_bc_packets and gms_bc_packets
1690: -- This procedure will be used when all records in gl_bc_packets (corresponding to sponsored)
1691: -- and not existing in gms_bc_packets.
1692: --============================================================================================
1693:
1694:
1693:
1694:
1695: -- =====================================================================================================
1696: -- R12 Funds Managment Uptake : New autonomous procedure to update failed result and status codes on
1697: -- gms and gl bc packets if burdenable raw cost calculation failed for atleast one record in packet.
1698: -- This called when calling_mode ='R' (Reserve)/'C' (Checkfunds)/'U' (unreserve) for AP/PO/REQ transactions.
1699: -- This procedure will be fired from main session procedure copy_gl_pkt_to_gms_pkt which in turn is
1700: -- called from GL main budgetory control API.
1701: -- =====================================================================================================
1733: p_status_code=> 'T',
1734: p_result_code=> 'F76');
1735:
1736: -- Bug : 2557041 - Added for IP check funds Enhancement
1737: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
1738: -- failed.
1739:
1740: -- R12 funds management Uptake : no update on gl_bc_packets required
1741: /*UPDATE gl_bc_packets
1736: -- Bug : 2557041 - Added for IP check funds Enhancement
1737: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
1738: -- failed.
1739:
1740: -- R12 funds management Uptake : no update on gl_bc_packets required
1741: /*UPDATE gl_bc_packets
1742: SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
1743: WHERE packet_id = P_packet_id; */
1744:
1737: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
1738: -- failed.
1739:
1740: -- R12 funds management Uptake : no update on gl_bc_packets required
1741: /*UPDATE gl_bc_packets
1742: SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
1743: WHERE packet_id = P_packet_id; */
1744:
1745: END IF;
1786: -- Input parameters : PLSQL tables storing data associated with gl pkts and AP/PO/REQ tables as the
1787: -- uncommited data in these transaction tables will not be accessible from
1788: -- autonomous session.
1789: -- p_gl_bc_pkt_spon_count - This parameter stores number of eligible gms records
1790: -- in gl_bc_packets
1791: -- p_return_code - 'F' - Insertion Failed / Failed status records inserted
1792: -- 'P' - Insertion passed and no failed status records inserted
1793: -- =====================================================================================================
1794:
2797: AND apsat.expenditure_type = et.expenditure_type;
2798:
2799:
2800: -- Local variables
2801: l_document_type gl_bc_packets.source_distribution_type%TYPE;
2802: l_gl_bc_pkt_spon_count NUMBER;
2803: L_RETURN_CODE VARCHAR2(1);
2804: l_mode VARCHAR2(1);
2805: l_prepay_exists VARCHAR2(1);
3477: l_mode);
3478: END IF;
3479:
3480: -- If code reaches this point means that data has been correctly transferred from
3481: -- gl_bc_packets to gms_bc_packets ..
3482:
3483: <
3484: NULL;
3485:
3647: boundary_code,
3648: time_phased_type_code,
3649: categorization_code,
3650: request_id,
3651: gl_bc_packets_rowid,
3652: bc_packet_id,
3653: parent_bc_packet_id,
3654: VENDOR_ID, --ADDED FOR BUG 16009601
3655: person_id,
3710: gbc.boundary_code,
3711: gbc.time_phased_type_code,
3712: gbc.categorization_code,
3713: gbc.request_id,
3714: gbc.gl_bc_packets_rowid,
3715: gms_bc_packets_s.NEXTVAL,
3716: decode(gbc.burden_adjustment_flag,'Y',gbc.parent_bc_packet_id,gbc.bc_packet_id),
3717: -- In case of burden adjustment flag, use parent_bc_packet_id on raw adjsutment line
3718: gbc.vendor_id, --ADDED FOR BUG 16009601
8212: g_mode := 'R';
8213: END IF;
8214:
8215: -- R12 FundsCheck Management Uptake : Deleted existing logic which was deriving g_doc_type and
8216: -- g_non_gms_txn based on records in GL_bc_packets/AP/PO/REQ tables.
8217: -- With new architecture the gl_bc_packets/AP/PO/REQ validations will be performed during
8218: -- insertion of GMS packets in main session and current logic derives document type based on
8219: -- the data inserted into gms_bc_packets for current packet_id.
8220:
8213: END IF;
8214:
8215: -- R12 FundsCheck Management Uptake : Deleted existing logic which was deriving g_doc_type and
8216: -- g_non_gms_txn based on records in GL_bc_packets/AP/PO/REQ tables.
8217: -- With new architecture the gl_bc_packets/AP/PO/REQ validations will be performed during
8218: -- insertion of GMS packets in main session and current logic derives document type based on
8219: -- the data inserted into gms_bc_packets for current packet_id.
8220:
8221: OPEN c_count_rec;
8316: pod.project_id project_id,
8317: pod.task_id task_id,
8318: adl.award_id award_id
8319: FROM gms_bc_packets pkt,
8320: gl_bc_packets gl,
8321: ap_invoice_distributions_all ap,
8322: po_distributions_all pod,
8323: gms_award_distributions adl
8324: WHERE pkt.packet_id = x_packet_id
8323: gms_award_distributions adl
8324: WHERE pkt.packet_id = x_packet_id
8325: AND pkt.document_type = 'AP'
8326: AND gl.packet_id = pkt.packet_id
8327: AND ROWIDTOCHAR (gl.ROWID) = pkt.gl_bc_packets_rowid
8328: AND gl.encumbrance_type_id <> p_inv_encumbrance_type_id
8329: AND NVL (pkt.entered_cr, 0) + NVL (pkt.entered_dr, 0) <> 0
8330: AND pod.po_distribution_id = ap.po_distribution_id
8331: AND ap.distribution_line_number= pkt.document_distribution_id
8539: Select gbc.packet_id
8540: from gms_bc_packets gbc
8541: where gbc.source_event_id in
8542: (select glbc.event_id
8543: from gl_bc_packets glbc
8544: where glbc.packet_id = p_packet_id)
8545: and gbc.status_code ='I'
8546: and gbc.document_type in ('AP','PO','REQ')
8547: and rownum =1;
8545: and gbc.status_code ='I'
8546: and gbc.document_type in ('AP','PO','REQ')
8547: and rownum =1;
8548:
8549: /* Bug 5285217 : Created the cursor c_gl_bc_pkt. This cursor selects all the records from gl_bc_packets for the current packet_id. */
8550: cursor c_gl_bc_pkt IS
8551: select p_packet_id packet_id,'P' status_code,
8552: glbc.session_id session_id,glbc.serial_id serial_id,
8553: glbc.je_category_name je_category_name,
8560: glet.encumbrance_type_key encumbrance_type_key,
8561: glbc.accounted_dr entered_dr , -- Bug 5614467
8562: glbc.accounted_cr entered_cr, -- Bug 5614467
8563: source_distribution_type
8564: from gl_bc_packets glbc,
8565: gl_encumbrance_types glet
8566: where glbc.packet_id = p_packet_id
8567: and glbc.encumbrance_type_id = glet.encumbrance_type_id;
8568:
8583: IF g_debug = 'Y' THEN
8584: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Previously establised packet is = '|| l_gms_packet_id,'C');
8585: END IF;
8586:
8587: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8588: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8589: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8590:
8591: -- Update gms_bc_packets data
8584: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Previously establised packet is = '|| l_gms_packet_id,'C');
8585: END IF;
8586:
8587: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8588: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8589: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8590:
8591: -- Update gms_bc_packets data
8592: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8585: END IF;
8586:
8587: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8588: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8589: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8590:
8591: -- Update gms_bc_packets data
8592: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8593: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8588: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8589: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8590:
8591: -- Update gms_bc_packets data
8592: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8593: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8594: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8595: the corresponding rowid on gl_bc_packets.*/
8596:
8589: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8590:
8591: -- Update gms_bc_packets data
8592: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8593: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8594: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8595: the corresponding rowid on gl_bc_packets.*/
8596:
8597: FOR glbcrec in c_gl_bc_pkt LOOP
8591: -- Update gms_bc_packets data
8592: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8593: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8594: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8595: the corresponding rowid on gl_bc_packets.*/
8596:
8597: FOR glbcrec in c_gl_bc_pkt LOOP
8598:
8599: IF g_debug = 'Y' THEN
8636: gbc.je_category_name,
8637: gbc.je_source_name,gbc.period_name,
8638: gbc.period_year,gbc.period_num,
8639: gbc.account_type,
8640: gl_bc_packets_rowid) =
8641: (select glbcrec.packet_id,glbcrec.status_code,
8642: glbcrec.session_id,glbcrec.serial_id,
8643: glbcrec.je_category_name,
8644: glbcrec.je_source_name,glbcrec.period_name,
8660: ))
8661: and gbc.document_type = decode(glbcrec.encumbrance_type_key,'Commitment','REQ'
8662: ,'Obligation','PO'
8663: ,'Invoices','AP')
8664: /* Bug 5285217 : For an Invoice Matched to a PO with Quantity Variance , there are two records in gl_bc_packets
8665: with encumbrance type as 'Invoices' (one for the invoice reserve and the other for the Quantity variance reserve).
8666: Also the packet_id,event_id and source_distribution_id_num_1 on both the records are same.
8667: So the 'gl_bc_packets_rowid IS NULL' and 'ROWNUM = 1' conditions are used to differentiate between
8668: the two records.
8663: ,'Invoices','AP')
8664: /* Bug 5285217 : For an Invoice Matched to a PO with Quantity Variance , there are two records in gl_bc_packets
8665: with encumbrance type as 'Invoices' (one for the invoice reserve and the other for the Quantity variance reserve).
8666: Also the packet_id,event_id and source_distribution_id_num_1 on both the records are same.
8667: So the 'gl_bc_packets_rowid IS NULL' and 'ROWNUM = 1' conditions are used to differentiate between
8668: the two records.
8669: For the first AP record (either invoice reserve record or the Quantity variance reserve record) in gl_bc_packets,
8670: the 'ROWNUM=1' and the (entered_dr-entered_cr) conditions are used to identify the corresponding record
8671: in gms_bc_packets. The (entered_dr-entered_cr) check is not suitable for the scenario in which the invoice amount and the quantity
8665: with encumbrance type as 'Invoices' (one for the invoice reserve and the other for the Quantity variance reserve).
8666: Also the packet_id,event_id and source_distribution_id_num_1 on both the records are same.
8667: So the 'gl_bc_packets_rowid IS NULL' and 'ROWNUM = 1' conditions are used to differentiate between
8668: the two records.
8669: For the first AP record (either invoice reserve record or the Quantity variance reserve record) in gl_bc_packets,
8670: the 'ROWNUM=1' and the (entered_dr-entered_cr) conditions are used to identify the corresponding record
8671: in gms_bc_packets. The (entered_dr-entered_cr) check is not suitable for the scenario in which the invoice amount and the quantity
8672: variance amount are same. But as the amounts are same for both the invoice reserve and quantity variance reserve
8673: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8672: variance amount are same. But as the amounts are same for both the invoice reserve and quantity variance reserve
8673: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8674: quantity variance record.
8675: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8676: corresponding record is not yet updated in gms_bc_packets) in gl_bc_packets , the 'gl_bc_packets_rowid IS NULL'
8677: condition is used to identify the corresponding record in gms_bc_packets.*/
8678: and gbc.gl_bc_packets_rowid IS NULL
8679: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8680: and ROWNUM = 1
8674: quantity variance record.
8675: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8676: corresponding record is not yet updated in gms_bc_packets) in gl_bc_packets , the 'gl_bc_packets_rowid IS NULL'
8677: condition is used to identify the corresponding record in gms_bc_packets.*/
8678: and gbc.gl_bc_packets_rowid IS NULL
8679: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8680: and ROWNUM = 1
8681: -- If it's PO mathed to an AP then for the PO reversal record in gl_bc_packets source_distribution_id_num_1 is populated as invoice_distribution_id
8682: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8677: condition is used to identify the corresponding record in gms_bc_packets.*/
8678: and gbc.gl_bc_packets_rowid IS NULL
8679: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8680: and ROWNUM = 1
8681: -- If it's PO mathed to an AP then for the PO reversal record in gl_bc_packets source_distribution_id_num_1 is populated as invoice_distribution_id
8682: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8683: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8684: -- This update is NOT for the PO reversal record in an AP matched to a PO scenario.
8685: and 1 >= (select count(distinct glbc1.encumbrance_type_id) -- This will return more than one count for the PO reversal record in an AP matched to a PO scenario.
8682: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8683: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8684: -- This update is NOT for the PO reversal record in an AP matched to a PO scenario.
8685: and 1 >= (select count(distinct glbc1.encumbrance_type_id) -- This will return more than one count for the PO reversal record in an AP matched to a PO scenario.
8686: from gl_bc_packets glbc1
8687: where glbc1.packet_id = p_packet_id
8688: and glbc1.event_id = gbc.source_event_id
8689: and glbc1.source_distribution_type = 'AP_INV_DIST'
8690: and gbc.document_type ='PO' );
8694: IF g_debug = 'Y' THEN
8695: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets except PO matched to an invoice '|| SQL%ROWCOUNT,'C');
8696: END IF;
8697:
8698: -- If it's PO mathed to an AP then for the PO reversal record in gl_bc_packets source_distribution_id_num_1 is populated as invoice_distribution_id
8699: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8700: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8701: -- This update is for the PO reversal records , which will be only records left with gbc.packet_id = l_gms_packet_id , in an AP matched to a PO scenario.
8702:
8705: gbc.session_id,gbc.serial_id,
8706: gbc.period_name,
8707: gbc.period_year,gbc.period_num,
8708: gbc.account_type,
8709: gl_bc_packets_rowid) =
8710: (select p_packet_id,'P',
8711: glbc.session_id,glbc.serial_id,
8712: glbc.period_name,
8713: glbc.period_year,glbc.period_num,
8712: glbc.period_name,
8713: glbc.period_year,glbc.period_num,
8714: glbc.account_type,
8715: rowidtochar(glbc.rowid)
8716: from gl_bc_packets glbc
8717: where glbc.packet_id = p_packet_id
8718: and glbc.event_id = gbc.source_event_id
8719: /* Start changes for bug#13975496 */
8720: and (( je_source_name ='Payables' and
8743: gbc.je_category_name,
8744: gbc.je_source_name,gbc.period_name,
8745: gbc.period_year,gbc.period_num,
8746: gbc.account_type,
8747: gl_bc_packets_rowid) =
8748: (select gbcparent.packet_id,'P',
8749: gbcparent.session_id,gbcparent.serial_id,
8750: gbcparent.je_category_name,
8751: gbcparent.je_source_name,gbcparent.period_name,
8750: gbcparent.je_category_name,
8751: gbcparent.je_source_name,gbcparent.period_name,
8752: gbcparent.period_year,gbcparent.period_num,
8753: gbcparent.account_type,
8754: gbcparent.gl_bc_packets_rowid
8755: from gms_bc_packets gbcparent
8756: where gbcparent.bc_packet_id = gbc.parent_bc_packet_id)
8757: where gbc.packet_id = l_gms_packet_id
8758: and gbc.status_code = 'I'
8755: from gms_bc_packets gbcparent
8756: where gbcparent.bc_packet_id = gbc.parent_bc_packet_id)
8757: where gbc.packet_id = l_gms_packet_id
8758: and gbc.status_code = 'I'
8759: and gbc.gl_bc_packets_rowid IS NULL
8760: /* Added for bug 11697170 */
8761: and gbc.parent_bc_packet_id is not null;
8762:
8763:
8838: g_packet_id||','||g_mode||','||x_partial,'C');
8839: END IF;
8840:
8841: -- ---------------------------------------------------------------------------------------------------------+
8842: -- 101: R12 Funds Management uptake : This procedure will synch data in gl_bc_packets to gms_bc_packets ..
8843: -- ---------------------------------------------------------------------------------------------------------+
8844: SYNCH_GMS_GL_PACKETS(x_packetid);
8845:
8846: -------------------------------------------------------------------------------+
8983: p_status_code=> 'T',
8984: p_result_code=> 'F76');
8985:
8986: -- Bug : 2557041 - Added for IP check funds Enhancement
8987: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
8988: -- failed.
8989:
8990: UPDATE gl_bc_packets
8991: SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
8986: -- Bug : 2557041 - Added for IP check funds Enhancement
8987: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
8988: -- failed.
8989:
8990: UPDATE gl_bc_packets
8991: SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
8992: WHERE packet_id = x_packetid;
8993:
8994: x_e_code := 'U'; -- Bug : 2557041 - Added , same as done for misc_gms_insert
9154: DELETE gms_bc_packet_arrival_order
9155: WHERE packet_id = x_packetid;
9156: END IF;
9157:
9158: -- Bug 2337897 : If any Unhandled exception occurs, them mark gl_bc_packets
9159: -- as Funds check failed
9160:
9161: If x_mode in ('R','U','C') and g_doc_type <> 'FAB' then -- not g_derived_mode
9162:
9159: -- as Funds check failed
9160:
9161: If x_mode in ('R','U','C') and g_doc_type <> 'FAB' then -- not g_derived_mode
9162:
9163: UPDATE gl_bc_packets SET
9164: result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F71',result_code)
9165: WHERE packet_id = x_packetid;
9166:
9167: End If;