[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;
1283: -- Duplicate Distribution lines in REQ/PO
1284: -- Match a PO with Invoice
1285: ----------------------------------------------------------------------------------------------------------
1286:
1287: -- R12 Funds management uptake : With R12 this API will get fired before inserting records into gl_bc_packets,
1288: -- hence modified the whole logic of this procedure .
1289: -- Logic is now based on global PLSQL variables populated by copy_gl_pkt_to_gms_pkt procedure
1290:
1291: ----------------------------------------------------------------------------------------------------------
1638: END misc_synch_adls;
1639:
1640: -- R12 Funds Management Uptake : Obsolete data_transfer_failure procedure as this logic is handled in copy_gl_pkt_to_gms_pkt
1641: --============================================================================================
1642: -- Bug 2899151 : This procedure will fail gl_bc_packets and gms_bc_packets
1643: -- This procedure will be used when all records in gl_bc_packets (corresponding to sponsored)
1644: -- and not existing in gms_bc_packets.
1645: --============================================================================================
1646:
1639:
1640: -- R12 Funds Management Uptake : Obsolete data_transfer_failure procedure as this logic is handled in copy_gl_pkt_to_gms_pkt
1641: --============================================================================================
1642: -- Bug 2899151 : This procedure will fail gl_bc_packets and gms_bc_packets
1643: -- This procedure will be used when all records in gl_bc_packets (corresponding to sponsored)
1644: -- and not existing in gms_bc_packets.
1645: --============================================================================================
1646:
1647:
1646:
1647:
1648: -- =====================================================================================================
1649: -- R12 Funds Managment Uptake : New autonomous procedure to update failed result and status codes on
1650: -- gms and gl bc packets if burdenable raw cost calculation failed for atleast one record in packet.
1651: -- This called when calling_mode ='R' (Reserve)/'C' (Checkfunds)/'U' (unreserve) for AP/PO/REQ transactions.
1652: -- This procedure will be fired from main session procedure copy_gl_pkt_to_gms_pkt which in turn is
1653: -- called from GL main budgetory control API.
1654: -- =====================================================================================================
1686: p_status_code=> 'T',
1687: p_result_code=> 'F76');
1688:
1689: -- Bug : 2557041 - Added for IP check funds Enhancement
1690: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
1691: -- failed.
1692:
1693: -- R12 funds management Uptake : no update on gl_bc_packets required
1694: /*UPDATE gl_bc_packets
1689: -- Bug : 2557041 - Added for IP check funds Enhancement
1690: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
1691: -- failed.
1692:
1693: -- R12 funds management Uptake : no update on gl_bc_packets required
1694: /*UPDATE gl_bc_packets
1695: SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
1696: WHERE packet_id = P_packet_id; */
1697:
1690: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
1691: -- failed.
1692:
1693: -- R12 funds management Uptake : no update on gl_bc_packets required
1694: /*UPDATE gl_bc_packets
1695: SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
1696: WHERE packet_id = P_packet_id; */
1697:
1698: END IF;
1739: -- Input parameters : PLSQL tables storing data associated with gl pkts and AP/PO/REQ tables as the
1740: -- uncommited data in these transaction tables will not be accessible from
1741: -- autonomous session.
1742: -- p_gl_bc_pkt_spon_count - This parameter stores number of eligible gms records
1743: -- in gl_bc_packets
1744: -- p_return_code - 'F' - Insertion Failed / Failed status records inserted
1745: -- 'P' - Insertion passed and no failed status records inserted
1746: -- =====================================================================================================
1747:
2526: AND apd.expenditure_type = et.expenditure_type;
2527:
2528:
2529: -- Local variables
2530: l_document_type gl_bc_packets.source_distribution_type%TYPE;
2531: l_gl_bc_pkt_spon_count NUMBER;
2532: L_RETURN_CODE VARCHAR2(1);
2533: l_mode VARCHAR2(1);
2534: l_prepay_exists VARCHAR2(1);
3206: l_mode);
3207: END IF;
3208:
3209: -- If code reaches this point means that data has been correctly transferred from
3210: -- gl_bc_packets to gms_bc_packets ..
3211:
3212: <
3213: NULL;
3214:
3345: boundary_code,
3346: time_phased_type_code,
3347: categorization_code,
3348: request_id,
3349: gl_bc_packets_rowid,
3350: bc_packet_id,
3351: parent_bc_packet_id,
3352: person_id,
3353: job_id,
3406: gbc.boundary_code,
3407: gbc.time_phased_type_code,
3408: gbc.categorization_code,
3409: gbc.request_id,
3410: gbc.gl_bc_packets_rowid,
3411: gms_bc_packets_s.NEXTVAL,
3412: decode(gbc.burden_adjustment_flag,'Y',gbc.parent_bc_packet_id,gbc.bc_packet_id),
3413: -- In case of burden adjustment flag, use parent_bc_packet_id on raw adjsutment line
3414: gbc.person_id,
7856: g_mode := 'R';
7857: END IF;
7858:
7859: -- R12 FundsCheck Management Uptake : Deleted existing logic which was deriving g_doc_type and
7860: -- g_non_gms_txn based on records in GL_bc_packets/AP/PO/REQ tables.
7861: -- With new architecture the gl_bc_packets/AP/PO/REQ validations will be performed during
7862: -- insertion of GMS packets in main session and current logic derives document type based on
7863: -- the data inserted into gms_bc_packets for current packet_id.
7864:
7857: END IF;
7858:
7859: -- R12 FundsCheck Management Uptake : Deleted existing logic which was deriving g_doc_type and
7860: -- g_non_gms_txn based on records in GL_bc_packets/AP/PO/REQ tables.
7861: -- With new architecture the gl_bc_packets/AP/PO/REQ validations will be performed during
7862: -- insertion of GMS packets in main session and current logic derives document type based on
7863: -- the data inserted into gms_bc_packets for current packet_id.
7864:
7865: OPEN c_count_rec;
7960: pod.project_id project_id,
7961: pod.task_id task_id,
7962: adl.award_id award_id
7963: FROM gms_bc_packets pkt,
7964: gl_bc_packets gl,
7965: ap_invoice_distributions_all ap,
7966: po_distributions_all pod,
7967: gms_award_distributions adl
7968: WHERE pkt.packet_id = x_packet_id
7967: gms_award_distributions adl
7968: WHERE pkt.packet_id = x_packet_id
7969: AND pkt.document_type = 'AP'
7970: AND gl.packet_id = pkt.packet_id
7971: AND ROWIDTOCHAR (gl.ROWID) = pkt.gl_bc_packets_rowid
7972: AND gl.encumbrance_type_id <> p_inv_encumbrance_type_id
7973: AND NVL (pkt.entered_cr, 0) + NVL (pkt.entered_dr, 0) <> 0
7974: AND pod.po_distribution_id = ap.po_distribution_id
7975: AND ap.distribution_line_number= pkt.document_distribution_id
8174: Select gbc.packet_id
8175: from gms_bc_packets gbc
8176: where gbc.source_event_id in
8177: (select glbc.event_id
8178: from gl_bc_packets glbc
8179: where glbc.packet_id = p_packet_id)
8180: and gbc.status_code ='I'
8181: and gbc.document_type in ('AP','PO','REQ')
8182: and rownum =1;
8180: and gbc.status_code ='I'
8181: and gbc.document_type in ('AP','PO','REQ')
8182: and rownum =1;
8183:
8184: /* Bug 5285217 : Created the cursor c_gl_bc_pkt. This cursor selects all the records from gl_bc_packets for the current packet_id. */
8185: cursor c_gl_bc_pkt IS
8186: select p_packet_id packet_id,'P' status_code,
8187: glbc.session_id session_id,glbc.serial_id serial_id,
8188: glbc.je_category_name je_category_name,
8195: glet.encumbrance_type_key encumbrance_type_key,
8196: glbc.accounted_dr entered_dr , -- Bug 5614467
8197: glbc.accounted_cr entered_cr, -- Bug 5614467
8198: source_distribution_type
8199: from gl_bc_packets glbc,
8200: gl_encumbrance_types glet
8201: where glbc.packet_id = p_packet_id
8202: and glbc.encumbrance_type_id = glet.encumbrance_type_id;
8203:
8218: IF g_debug = 'Y' THEN
8219: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Previously establised packet is = '|| l_gms_packet_id,'C');
8220: END IF;
8221:
8222: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8223: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8224: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8225:
8226: -- Update gms_bc_packets data
8219: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Previously establised packet is = '|| l_gms_packet_id,'C');
8220: END IF;
8221:
8222: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8223: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8224: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8225:
8226: -- Update gms_bc_packets data
8227: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8220: END IF;
8221:
8222: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8223: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8224: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8225:
8226: -- Update gms_bc_packets data
8227: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8228: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8223: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8224: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8225:
8226: -- Update gms_bc_packets data
8227: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8228: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8229: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8230: the corresponding rowid on gl_bc_packets.*/
8231:
8224: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8225:
8226: -- Update gms_bc_packets data
8227: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8228: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8229: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8230: the corresponding rowid on gl_bc_packets.*/
8231:
8232: FOR glbcrec in c_gl_bc_pkt LOOP
8226: -- Update gms_bc_packets data
8227: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8228: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8229: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8230: the corresponding rowid on gl_bc_packets.*/
8231:
8232: FOR glbcrec in c_gl_bc_pkt LOOP
8233:
8234: IF g_debug = 'Y' THEN
8271: gbc.je_category_name,
8272: gbc.je_source_name,gbc.period_name,
8273: gbc.period_year,gbc.period_num,
8274: gbc.account_type,
8275: gl_bc_packets_rowid) =
8276: (select glbcrec.packet_id,glbcrec.status_code,
8277: glbcrec.session_id,glbcrec.serial_id,
8278: glbcrec.je_category_name,
8279: glbcrec.je_source_name,glbcrec.period_name,
8295: ))
8296: and gbc.document_type = decode(glbcrec.encumbrance_type_key,'Commitment','REQ'
8297: ,'Obligation','PO'
8298: ,'Invoices','AP')
8299: /* Bug 5285217 : For an Invoice Matched to a PO with Quantity Variance , there are two records in gl_bc_packets
8300: with encumbrance type as 'Invoices' (one for the invoice reserve and the other for the Quantity variance reserve).
8301: Also the packet_id,event_id and source_distribution_id_num_1 on both the records are same.
8302: So the 'gl_bc_packets_rowid IS NULL' and 'ROWNUM = 1' conditions are used to differentiate between
8303: the two records.
8298: ,'Invoices','AP')
8299: /* Bug 5285217 : For an Invoice Matched to a PO with Quantity Variance , there are two records in gl_bc_packets
8300: with encumbrance type as 'Invoices' (one for the invoice reserve and the other for the Quantity variance reserve).
8301: Also the packet_id,event_id and source_distribution_id_num_1 on both the records are same.
8302: So the 'gl_bc_packets_rowid IS NULL' and 'ROWNUM = 1' conditions are used to differentiate between
8303: the two records.
8304: For the first AP record (either invoice reserve record or the Quantity variance reserve record) in gl_bc_packets,
8305: the 'ROWNUM=1' and the (entered_dr-entered_cr) conditions are used to identify the corresponding record
8306: in gms_bc_packets. The (entered_dr-entered_cr) check is not suitable for the scenario in which the invoice amount and the quantity
8300: with encumbrance type as 'Invoices' (one for the invoice reserve and the other for the Quantity variance reserve).
8301: Also the packet_id,event_id and source_distribution_id_num_1 on both the records are same.
8302: So the 'gl_bc_packets_rowid IS NULL' and 'ROWNUM = 1' conditions are used to differentiate between
8303: the two records.
8304: For the first AP record (either invoice reserve record or the Quantity variance reserve record) in gl_bc_packets,
8305: the 'ROWNUM=1' and the (entered_dr-entered_cr) conditions are used to identify the corresponding record
8306: in gms_bc_packets. The (entered_dr-entered_cr) check is not suitable for the scenario in which the invoice amount and the quantity
8307: variance amount are same. But as the amounts are same for both the invoice reserve and quantity variance reserve
8308: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8307: variance amount are same. But as the amounts are same for both the invoice reserve and quantity variance reserve
8308: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8309: quantity variance record.
8310: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8311: corresponding record is not yet updated in gms_bc_packets) in gl_bc_packets , the 'gl_bc_packets_rowid IS NULL'
8312: condition is used to identify the corresponding record in gms_bc_packets.*/
8313: and gbc.gl_bc_packets_rowid IS NULL
8314: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8315: and ROWNUM = 1
8309: quantity variance record.
8310: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8311: corresponding record is not yet updated in gms_bc_packets) in gl_bc_packets , the 'gl_bc_packets_rowid IS NULL'
8312: condition is used to identify the corresponding record in gms_bc_packets.*/
8313: and gbc.gl_bc_packets_rowid IS NULL
8314: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8315: and ROWNUM = 1
8316: -- 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
8317: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8312: condition is used to identify the corresponding record in gms_bc_packets.*/
8313: and gbc.gl_bc_packets_rowid IS NULL
8314: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8315: and ROWNUM = 1
8316: -- 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
8317: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8318: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8319: -- This update is NOT for the PO reversal record in an AP matched to a PO scenario.
8320: 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.
8317: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8318: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8319: -- This update is NOT for the PO reversal record in an AP matched to a PO scenario.
8320: 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.
8321: from gl_bc_packets glbc1
8322: where glbc1.packet_id = p_packet_id
8323: and glbc1.event_id = gbc.source_event_id
8324: and glbc1.source_distribution_type = 'AP_INV_DIST'
8325: and gbc.document_type ='PO' );
8329: IF g_debug = 'Y' THEN
8330: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets except PO matched to an invoice '|| SQL%ROWCOUNT,'C');
8331: END IF;
8332:
8333: -- 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
8334: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8335: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8336: -- 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.
8337:
8340: gbc.session_id,gbc.serial_id,
8341: gbc.period_name,
8342: gbc.period_year,gbc.period_num,
8343: gbc.account_type,
8344: gl_bc_packets_rowid) =
8345: (select p_packet_id,'P',
8346: glbc.session_id,glbc.serial_id,
8347: glbc.period_name,
8348: glbc.period_year,glbc.period_num,
8347: glbc.period_name,
8348: glbc.period_year,glbc.period_num,
8349: glbc.account_type,
8350: rowidtochar(glbc.rowid)
8351: from gl_bc_packets glbc
8352: where glbc.packet_id = p_packet_id
8353: and glbc.event_id = gbc.source_event_id
8354: and glbc.source_distribution_id_num_1 <> gbc.document_distribution_id
8355: and glbc.encumbrance_type_id IN (SELECT glenc.encumbrance_type_id --Seeded encumbrance type for PO
8371: gbc.je_category_name,
8372: gbc.je_source_name,gbc.period_name,
8373: gbc.period_year,gbc.period_num,
8374: gbc.account_type,
8375: gl_bc_packets_rowid) =
8376: (select gbcparent.packet_id,'P',
8377: gbcparent.session_id,gbcparent.serial_id,
8378: gbcparent.je_category_name,
8379: gbcparent.je_source_name,gbcparent.period_name,
8378: gbcparent.je_category_name,
8379: gbcparent.je_source_name,gbcparent.period_name,
8380: gbcparent.period_year,gbcparent.period_num,
8381: gbcparent.account_type,
8382: gbcparent.gl_bc_packets_rowid
8383: from gms_bc_packets gbcparent
8384: where gbcparent.bc_packet_id = gbc.parent_bc_packet_id)
8385: where gbc.packet_id = l_gms_packet_id
8386: and gbc.status_code = 'I'
8383: from gms_bc_packets gbcparent
8384: where gbcparent.bc_packet_id = gbc.parent_bc_packet_id)
8385: where gbc.packet_id = l_gms_packet_id
8386: and gbc.status_code = 'I'
8387: and gbc.gl_bc_packets_rowid IS NULL;
8388:
8389: IF g_debug = 'Y' THEN
8390: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets for prepayment burden adjustment lines '|| SQL%ROWCOUNT,'C');
8391: END IF;
8463: g_packet_id||','||g_mode||','||x_partial,'C');
8464: END IF;
8465:
8466: -- ---------------------------------------------------------------------------------------------------------+
8467: -- 101: R12 Funds Management uptake : This procedure will synch data in gl_bc_packets to gms_bc_packets ..
8468: -- ---------------------------------------------------------------------------------------------------------+
8469: SYNCH_GMS_GL_PACKETS(x_packetid);
8470:
8471: -------------------------------------------------------------------------------+
8608: p_status_code=> 'T',
8609: p_result_code=> 'F76');
8610:
8611: -- Bug : 2557041 - Added for IP check funds Enhancement
8612: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
8613: -- failed.
8614:
8615: UPDATE gl_bc_packets
8616: SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
8611: -- Bug : 2557041 - Added for IP check funds Enhancement
8612: -- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
8613: -- failed.
8614:
8615: UPDATE gl_bc_packets
8616: SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
8617: WHERE packet_id = x_packetid;
8618:
8619: x_e_code := 'U'; -- Bug : 2557041 - Added , same as done for misc_gms_insert
8779: DELETE gms_bc_packet_arrival_order
8780: WHERE packet_id = x_packetid;
8781: END IF;
8782:
8783: -- Bug 2337897 : If any Unhandled exception occurs, them mark gl_bc_packets
8784: -- as Funds check failed
8785:
8786: If x_mode in ('R','U','C') and g_doc_type <> 'FAB' then -- not g_derived_mode
8787:
8784: -- as Funds check failed
8785:
8786: If x_mode in ('R','U','C') and g_doc_type <> 'FAB' then -- not g_derived_mode
8787:
8788: UPDATE gl_bc_packets SET
8789: result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F71',result_code)
8790: WHERE packet_id = x_packetid;
8791:
8792: End If;