[Home] [Help]
144: End If;
145:
146: Mark_RCV_PAflag();
147:
148: IF (G_RCV_TRANSACTIONS_MARKED_O > 0) THEN
149:
150: G_err_stage := 'Before calling transfer receipts to pa procedure';
151: If g_body_debug_mode = 'Y' Then
152: write_log(LOG, 'No rcv transctions to be transferred.');
559: G_NUM_AP_VARIANCE_MARKED_W :=0;
560: G_NUM_AP_VARIANCE_PROCESSED :=0;
561:
562: --Added for PO RECEIPT
563: G_RCV_TRANSACTIONS_MARKED_O :=0;
564: G_RCV_TRANSACTIONS_MARKED_J :=0;
565: G_RCV_TRANSACTIONS_MARKED_NULL :=0;
566: G_RCV_TRANSACTIONS_MARKED_G :=0;
567: G_NUM_RCV_TXN_PROCESSED :=0;
560: G_NUM_AP_VARIANCE_PROCESSED :=0;
561:
562: --Added for PO RECEIPT
563: G_RCV_TRANSACTIONS_MARKED_O :=0;
564: G_RCV_TRANSACTIONS_MARKED_J :=0;
565: G_RCV_TRANSACTIONS_MARKED_NULL :=0;
566: G_RCV_TRANSACTIONS_MARKED_G :=0;
567: G_NUM_RCV_TXN_PROCESSED :=0;
568: G_NUM_RCVTAX_PROCESSED :=0;
561:
562: --Added for PO RECEIPT
563: G_RCV_TRANSACTIONS_MARKED_O :=0;
564: G_RCV_TRANSACTIONS_MARKED_J :=0;
565: G_RCV_TRANSACTIONS_MARKED_NULL :=0;
566: G_RCV_TRANSACTIONS_MARKED_G :=0;
567: G_NUM_RCV_TXN_PROCESSED :=0;
568: G_NUM_RCVTAX_PROCESSED :=0;
569:
562: --Added for PO RECEIPT
563: G_RCV_TRANSACTIONS_MARKED_O :=0;
564: G_RCV_TRANSACTIONS_MARKED_J :=0;
565: G_RCV_TRANSACTIONS_MARKED_NULL :=0;
566: G_RCV_TRANSACTIONS_MARKED_G :=0;
567: G_NUM_RCV_TXN_PROCESSED :=0;
568: G_NUM_RCVTAX_PROCESSED :=0;
569:
570: /* restore the old G_err_stack */
3917: tieback_AP_ER(G_TRANSACTION_SOURCE, G_AP_FRT_BATCH_NAME, G_AP_FRT_INTERFACE_ID);
3918:
3919: END IF; /* IF (nvl(v_num_inv_frt_lines_fetched,0) > 0*/
3920:
3921: write_log(LOG,'Calling tieback for locked RCV transactions');
3922: tieback_locked_rcvtxn;
3923:
3924: /*** Commented 3922679 *
3925: G_err_stage := 'Before calling commit';
4272:
4273: G_err_stage := 'Within calling lock_rcv_txn';
4274: write_log(LOG, G_err_stage);
4275:
4276: --pricing changes, updating sub_ledger instead of rcv_transactions
4277: -- Modified this update for bug 6825742
4278:
4279: UPDATE rcv_receiving_sub_ledger rcv_sub
4280: SET rcv_sub.pa_addition_flag = 'L'
4289: /* UPDATE rcv_receiving_sub_ledger rcv_sub
4290: SET rcv_sub.pa_addition_flag = 'L'
4291: WHERE rcv_sub.pa_addition_flag = 'N'
4292: AND EXISTS (SELECT transaction_id
4293: FROM rcv_transactions rcv_txn
4294: WHERE rcv_txn.transaction_id = rcv_sub.rcv_transaction_id
4295: AND rcv_txn.po_distribution_id = p_po_distribution_id ); commented for bug 6825742*/
4296:
4297: l_num_rows := SQL%ROWCOUNT;
4295: AND rcv_txn.po_distribution_id = p_po_distribution_id ); commented for bug 6825742*/
4296:
4297: l_num_rows := SQL%ROWCOUNT;
4298:
4299: write_log(LOG,'number of RCV transactions locked:'||l_num_rows);
4300:
4301: EXCEPTION
4302: WHEN NO_DATA_FOUND THEN
4303: NULL;
4323: WHERE rcv_sub.pa_addition_flag = 'L';
4324:
4325: l_num_rows := SQL%ROWCOUNT;
4326:
4327: write_log(LOG,'number of RCV transactions unlocked:'||l_num_rows);
4328:
4329: EXCEPTION
4330: WHEN NO_DATA_FOUND THEN
4331: NULL;
5546:
5547: BEGIN
5548:
5549: G_err_stack := G_err_stack || '->PAAPIMP_PKG.mark_RCV_PAflag';
5550: write_log(LOG, 'UPDATING RCV_TRANSACTIONS -Marking Process');
5551:
5552: /* mark all the rcv sub ledger records pa_addition_flag to NULL for any rcv transactions
5553: that are non-project related or with a non-EXPENSE destination_type_code,which is not a return or is not actual*/
5554: -- This update has been moved to pa_po_integration_utils.update_pa_addition_flag.
5548:
5549: G_err_stack := G_err_stack || '->PAAPIMP_PKG.mark_RCV_PAflag';
5550: write_log(LOG, 'UPDATING RCV_TRANSACTIONS -Marking Process');
5551:
5552: /* mark all the rcv sub ledger records pa_addition_flag to NULL for any rcv transactions
5553: that are non-project related or with a non-EXPENSE destination_type_code,which is not a return or is not actual*/
5554: -- This update has been moved to pa_po_integration_utils.update_pa_addition_flag.
5555:
5556:
5553: that are non-project related or with a non-EXPENSE destination_type_code,which is not a return or is not actual*/
5554: -- This update has been moved to pa_po_integration_utils.update_pa_addition_flag.
5555:
5556:
5557: /* mark rcv sub ledger records pa_addition_flag to O/J for rcv transactions that are
5558: project related and fit the criteria to be pulled into Projects.
5559: Divided into project_ID IS NULL and NOT NULL section for better performance
5560: */
5561:
5575: rcv_sub.program_application_id = G_PROG_APPL_ID,
5576: rcv_sub.program_update_date = SYSDATE
5577: WHERE exists
5578: (SELECT 1 --rcv_sub1.ROWID --Removed /*+ leading(po_dist) */ for Bug5262594
5579: FROM Rcv_Transactions rcv_txn,
5580: PO_Distributions po_dist
5581: WHERE ((rcv_txn.destination_type_code ='EXPENSE' )
5582: OR (rcv_txn.destination_type_code = 'RECEIVING'
5583: AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING'))))
5602: )
5603: AND rcv_sub.pa_addition_flag IN ('N','I')
5604: AND rcv_sub.actual_flag = 'A';
5605:
5606: G_RCV_TRANSACTIONS_MARKED_O := SQL%ROWCOUNT;
5607: write_log(LOG, 'Number of rcvtxn marked to O or J:' || to_char(SQL%ROWCOUNT));
5608:
5609:
5610: ELSIF G_PROJECT_ID IS NULL THEN
5623: rcv_sub.program_application_id = G_PROG_APPL_ID,
5624: rcv_sub.program_update_date = SYSDATE
5625: WHERE exists
5626: (SELECT 1 --rcv_sub1.ROWID --Removed /*+ leading(po_dist) */ for Bug5262594
5627: FROM Rcv_Transactions rcv_txn,
5628: PO_Distributions po_dist
5629: WHERE ((rcv_txn.destination_type_code ='EXPENSE' )
5630: OR (rcv_txn.destination_type_code = 'RECEIVING'
5631: AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING'))))
5650: AND rcv_sub.pa_addition_flag IN ('N','I')
5651: AND rcv_sub.actual_flag = 'A';
5652:
5653:
5654: G_RCV_TRANSACTIONS_MARKED_O := SQL%ROWCOUNT;
5655: write_log(LOG, 'Number of rcvtxn marked to O or J:' || to_char(SQL%ROWCOUNT));
5656:
5657: END IF;
5658:
6283: BEGIN
6284:
6285: SELECT primary_quantity, amount
6286: INTO l_primary_quantity, l_amount
6287: FROM rcv_transactions
6288: WHERE transaction_id = l_parent_rcv_id_tbl(i) ;
6289:
6290: IF ((( ( l_quantity_tbl(i) <> 0 AND l_primary_quantity = l_quantity_tbl(i)) OR
6291: ( l_amount = l_amount_tbl(i))) AND
6394: BEGIN
6395:
6396: SELECT primary_quantity, amount
6397: INTO l_primary_quantity, l_amount
6398: FROM rcv_transactions
6399: WHERE transaction_id = l_parent_rcv_id_tbl(i) ;
6400:
6401: IF ((( ( l_quantity_tbl(i) <> 0 AND l_primary_quantity = l_quantity_tbl(i)) OR
6402: ( l_amount = l_amount_tbl(i))) AND
6666: BEGIN
6667:
6668: SELECT primary_quantity, nvl(amount,0)
6669: INTO l_primary_quantity, l_amount
6670: FROM rcv_transactions
6671: WHERE transaction_id = l_parent_rcv_id_tbl(i) ;
6672:
6673: -- Added a new variable l_po_tax_qty_tbl which will store the original po qty since the l_quantity_tbl
6674: -- variable gets overwritten by the raw cost
7112: v_status VARCHAR2(15);
7113:
7114: BEGIN
7115:
7116: G_err_stage:='Clearing PLSQL tables in RCV transactions tieback';
7117: write_log(LOG, G_err_stage);
7118:
7119: l_sys_ref1_tbl.delete;
7120: l_sys_ref2_tbl.delete;
7136: v_status VARCHAR2(15);
7137:
7138: BEGIN
7139:
7140: G_err_stage:='Within process_tieback of RCV Transactions tieback';
7141: write_log(LOG, G_err_stage);
7142:
7143: FOR i IN l_sys_ref1_tbl.FIRST..l_sys_ref1_tbl.LAST LOOP
7144:
7142:
7143: FOR i IN l_sys_ref1_tbl.FIRST..l_sys_ref1_tbl.LAST LOOP
7144:
7145: /* If transaction import stamps the record to be 'A' then
7146: update pa_addition_flag of RCV transactions to 'Y'.
7147: If transaction import leaves the record to be 'P' then
7148: update pa_addition_flag of RCV transactions to 'N' or 'I'.
7149: If transaction import stamps the record to be 'R' then
7150: update pa_addition_flag of RCV transactions to 'N' or 'I'.*/
7144:
7145: /* If transaction import stamps the record to be 'A' then
7146: update pa_addition_flag of RCV transactions to 'Y'.
7147: If transaction import leaves the record to be 'P' then
7148: update pa_addition_flag of RCV transactions to 'N' or 'I'.
7149: If transaction import stamps the record to be 'R' then
7150: update pa_addition_flag of RCV transactions to 'N' or 'I'.*/
7151:
7152: write_log(LOG,'Tying back transaction ID: '||l_sys_ref4_tbl(i)||
7146: update pa_addition_flag of RCV transactions to 'Y'.
7147: If transaction import leaves the record to be 'P' then
7148: update pa_addition_flag of RCV transactions to 'N' or 'I'.
7149: If transaction import stamps the record to be 'R' then
7150: update pa_addition_flag of RCV transactions to 'N' or 'I'.*/
7151:
7152: write_log(LOG,'Tying back transaction ID: '||l_sys_ref4_tbl(i)||
7153: 'trc src: '||l_txn_src_tbl(i));
7154:
7192: v_status VARCHAR2(15);
7193:
7194: BEGIN
7195:
7196: G_err_stage:=('Within bulk update of RCV transactions tieback');
7197: write_log(LOG, G_err_stage);
7198:
7199: FORALL i IN l_sys_ref1_tbl.FIRST..l_sys_ref1_tbl.LAST
7200:
7206: AND (rcv_sub.accounting_event_id = l_rcv_acct_evt_id_tbl(i) OR rcv_sub.accounting_event_id IS NULL)
7207: AND rcv_sub.code_combination_id = l_dr_ccid_tbl(i)
7208: /* Start added for bug#6015451 */
7209: AND EXISTS
7210: (SELECT 1 from rcv_transactions rcv_txn WHERE
7211: rcv_txn.transaction_id = l_sys_ref4_tbl(i)
7212: AND ( (rcv_txn.destination_type_code = 'EXPENSE' AND
7213: rcv_txn.transaction_type <> 'RETURN TO RECEIVING' AND rcv_sub.entered_dr is NOT NULL
7214: ) OR
7220: /* Ends added for bug#6015451 */
7221:
7222:
7223: l_rows_tiebacked := SQL%ROWCOUNT;
7224: write_log(LOG,'Number of RCV transactions tied back: '||l_rows_tiebacked);
7225:
7226: EXCEPTION
7227: WHEN OTHERS THEN
7228: G_err_stage:= 'Failed during bulk update of RCV transactions tieback';
7224: write_log(LOG,'Number of RCV transactions tied back: '||l_rows_tiebacked);
7225:
7226: EXCEPTION
7227: WHEN OTHERS THEN
7228: G_err_stage:= 'Failed during bulk update of RCV transactions tieback';
7229: write_log(LOG, G_err_stage);
7230: G_err_code := SQLCODE;
7231: raise;
7232:
7509: WHERE apdist1.pa_addition_flag IN ('F', 'G')
7510: AND apdist1.po_distribution_id = dist.po_distribution_id
7511: )
7512: OR EXISTS ( SELECT rcv_txn.po_distribution_id
7513: FROM rcv_transactions rcv_txn
7514: , rcv_receiving_sub_ledger rcv_sub
7515: WHERE rcv_txn.po_distribution_id = dist.po_distribution_id
7516: AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
7517: AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
7638: WHERE apdist1.pa_addition_flag IN ('F', 'G')
7639: AND apdist1.po_distribution_id = dist.po_distribution_id
7640: )
7641: OR EXISTS ( SELECT rcv_txn.po_distribution_id
7642: FROM rcv_transactions rcv_txn
7643: , rcv_receiving_sub_ledger rcv_sub
7644: WHERE rcv_txn.po_distribution_id = dist.po_distribution_id
7645: AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
7646: AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
11352: SELECT 'X'
11353: INTO l_Dummy
11354: FROM DUAL
11355: WHERE EXISTS ( SELECT NULL
11356: FROM rcv_transactions rcv_txn2
11357: ,rcv_receiving_sub_ledger rcv_sub2
11358: WHERE rcv_sub2.rcv_transaction_id = rcv_txn2.transaction_id
11359: AND rcv_txn2.po_distribution_id = P_Po_Distribution_Id
11360: AND rcv_sub2.pa_addition_flag = 'G'