[Home] [Help]
207: Problem - Cursor c_regime where clause was wrongly specified which
208: resulting in failure in generation of rtv accounting .....
209:
210: 18. 21/06/2007 brathod, for bug# 6109941, File Version 120.12, 120.13
211: 1. Removed update of ATTRIBUTE (DFF) columns of RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES table
212: 2. added excise_invoice_no and excise_invoice_date in cursor c_shipment_lines
213: 3. Changed reference of r_trx.excise_invoice_no and r_trx.excise_invoice_date to
214: rec_lines.excise_invoice_no and rec_lines.excise_invoice_date resp.
215:
423:
424: PROCEDURE generate_schedule(
425: p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT NULL,
426: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
427: p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
428: p_transaction_id IN rcv_transactions.transaction_id%TYPE DEFAULT NULL,
429: p_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE DEFAULT NULL,
430: p_tax_id IN JAI_CMN_TAXES_ALL.tax_id%TYPE DEFAULT NULL,
431: p_override IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
436: p_simulate_flag IN VARCHAR2 DEFAULT 'N' -- Date 28/05/2007 sacsethi for bug 6071533 Change default value from null to N
437: )
438: IS
439: CURSOR cur_lines(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
440: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
441: IS
442: SELECT shipment_header_id, shipment_line_id
443: FROM JAI_RCV_LINES
444: WHERE shipment_header_id = NVL(cp_shipment_header_id, shipment_header_id)
444: WHERE shipment_header_id = NVL(cp_shipment_header_id, shipment_header_id)
445: AND shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id)
446: ORDER BY shipment_line_id;
447:
448: CURSOR cur_txns(cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
449: cp_transaction_id IN rcv_transactions.transaction_id%TYPE)
450: IS
451: SELECT transaction_id,
452: transaction_type,
491: AND a.modvat_flag = 'Y'
492: --Added for Bug#4950914
493: AND NVL(a.tax_amount,0) <> 0;
494:
495: CURSOR cur_term(cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
496: IS
497: SELECT term_id, rcv_rgm_line_id, receipt_date
498: FROM jai_rcv_rgm_lines
499: WHERE shipment_line_id = cp_shipment_line_id;
718: END generate_schedule;
719:
720: PROCEDURE insert_rcv_lines(
721: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
722: p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
723: p_transaction_id IN rcv_transactions.transaction_id%TYPE,
724: p_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE,
725: p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
726: p_process_status OUT NOCOPY VARCHAR2,
746: lv_item_class jai_rgm_lookups.attribute_code%TYPE;
747: ln_term_id NUMBER;
748: ln_location_id NUMBER(15);
749:
750: CURSOR c_tax_amount (cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
751: IS
752: SELECT nvl(rtl.tax_amount, 0) tax_amount,
753: nvl(rtl.modvat_flag, 'N') modvat_flag,
754: nvl(rtl.currency, jai_constants.func_curr) currency,
763: from jai_regime_tax_types_v
764: where regime_code = jai_constants.vat_regime);
765:
766: CURSOR c_shipment_lines(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
767: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
768: IS
769: SELECT a.shipment_line_id, a.transaction_id, a.inventory_item_id, a.receipt_num, b.line_num
770: , excise_invoice_no, excise_invoice_date -- brathod, Bug# 6109941
771: FROM JAI_RCV_LINES a,
768: IS
769: SELECT a.shipment_line_id, a.transaction_id, a.inventory_item_id, a.receipt_num, b.line_num
770: , excise_invoice_no, excise_invoice_date -- brathod, Bug# 6109941
771: FROM JAI_RCV_LINES a,
772: rcv_shipment_lines b
773: WHERE a.shipment_header_id = b.shipment_header_id
774: AND a.shipment_line_id = b.shipment_line_id
775: AND a.shipment_header_id = NVL(cp_shipment_header_id,a.shipment_header_id)
776: AND a.shipment_line_id = NVL(cp_shipment_line_id, a.shipment_line_id);
788: cannot be null.
789: */
790:
791: CURSOR c_receipt_source_code(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
792: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
793: IS
794: SELECT receipt_source_code
795: FROM rcv_shipment_headers a,
796: rcv_shipment_lines b
792: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
793: IS
794: SELECT receipt_source_code
795: FROM rcv_shipment_headers a,
796: rcv_shipment_lines b
797: WHERE a.shipment_header_id = b.shipment_header_id
798: AND a.shipment_header_id = cp_shipment_header_id
799: AND b.shipment_line_id = cp_shipment_line_id;
800:
1093: END insert_rcv_lines;
1094:
1095: PROCEDURE update_rcv_lines(
1096: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
1097: p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
1098: p_receipt_num IN JAI_RCV_LINES.receipt_num%TYPE DEFAULT NULL,
1099: p_recoverable_amount IN jai_rcv_rgm_lines.recoverable_amount%TYPE DEFAULT NULL,
1100: p_recovered_amount IN jai_rcv_rgm_lines.recovered_amount%TYPE DEFAULT NULL,
1101: p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT -999,
1113: ln_recovered_amount NUMBER;
1114: lv_process_status_flag VARCHAR2(2);
1115:
1116: CURSOR c_total_amount(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
1117: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
1118: IS
1119: SELECT NVL(SUM(installment_amount),0) recoverable_amount,
1120: NVL(SUM(claimed_amount),0) recovered_amount
1121: FROM jai_rcv_rgm_claims
1248: * Removed cursor c_org_info and variable r_org_info
1249: * and implemented caching logic.
1250: */
1251: --cursor added by Ramananda for Bug#4530112
1252: CURSOR c_rcv_rgm_lines(cp_shipment_line_id rcv_shipment_lines.shipment_line_id%TYPE)
1253: IS
1254: SELECT *
1255: FROM jai_rcv_rgm_lines
1256: WHERE shipment_line_id = cp_shipment_line_id;
1816: p_regime_regno IN VARCHAR2 DEFAULT NULL,
1817: p_organization_id IN hr_all_organization_units.organization_id%TYPE DEFAULT NULL,
1818: p_location_id IN hr_locations_all.location_id%TYPE DEFAULT NULL,
1819: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
1820: p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
1821: p_batch_id IN JAI_RCV_RGM_LINES.BATCH_NUM%TYPE DEFAULT NULL,
1822: p_force IN VARCHAR2 DEFAULT NULL,
1823: p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE,
1824: p_invoice_date IN JAI_RCV_TRANSACTIONS.vat_invoice_date%TYPE,
1878: WHERE regime_id = NVL(cp_regime_id, regime_id)
1879: AND regime_code = NVL(cp_regime_code, regime_code);
1880:
1881: CURSOR c_claim_schedule( cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
1882: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
1883: cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE,
1884: cp_installment_no IN jai_rcv_rgm_claims.installment_no%TYPE)
1885: IS
1886: SELECT MIN(claim_schedule_id) claim_schedule_id
2320: END process_claim;
2321:
2322: /*PROCEDURE process_no_claim(
2323: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
2324: p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
2325: p_batch_id IN jai_rcv_rgm_lines.batch_id%TYPE DEFAULT NULL,
2326: p_process_status OUT NOCOPY VARCHAR2,
2327: p_process_message OUT NOCOPY VARCHAR2)*/
2328: --commented the above for Bug#4950914
2327: p_process_message OUT NOCOPY VARCHAR2)*/
2328: --commented the above for Bug#4950914
2329: PROCEDURE process_no_claim(
2330: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
2331: p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
2332: p_batch_id IN jai_rcv_rgm_lines.batch_num%TYPE DEFAULT NULL,
2333: p_regime_regno IN VARCHAR2 DEFAULT NULL,
2334: p_organization_id IN hr_all_organization_units.organization_id%TYPE DEFAULT NULL,
2335: p_location_id IN hr_locations_all.location_id%TYPE DEFAULT NULL,
2337: p_process_message OUT NOCOPY VARCHAR2,
2338: p_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL )
2339: IS
2340: /*CURSOR c_shipment_lines(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2341: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
2342: cp_batch_id IN jai_rcv_rgm_lines.batch_id%TYPE)
2343: IS
2344: SELECT shipment_header_id,
2345: shipment_line_id,
2418: AND a.tax_id = b.tax_id
2419: AND NVL(a.modvat_flag,'N') = 'Y';
2420:
2421: CURSOR c_receive_transaction(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2422: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
2423: IS
2424: SELECT transaction_id,
2425: organization_id,
2426: location_id,
2448: lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type;
2449: lv_ttype_rtr JAI_RCV_TRANSACTIONS.transaction_type%type;
2450:
2451: CURSOR c_receive_correct_txns(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE, /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2452: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
2453: IS
2454: SELECT transaction_id,
2455: tax_transaction_id
2456: FROM JAI_RCV_TRANSACTIONS
2463: )
2464: ORDER BY transaction_id;
2465:
2466: CURSOR c_deliver_rtr_txns(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2467: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE) /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2468: IS
2469: SELECT transaction_id,
2470: tax_transaction_id,
2471: SIGN(quantity) quantity_multiplier
2481:
2482: -- Added by Jia for FP Bug#8805693, Begin
2483: --------------------------------------------------------
2484: CURSOR cur_check_projects (cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2485: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
2486: IS
2487: SELECT 1
2488: FROM po_distributions_all pda,
2489: rcv_shipment_lines rsl,
2485: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
2486: IS
2487: SELECT 1
2488: FROM po_distributions_all pda,
2489: rcv_shipment_lines rsl,
2490: po_headers_all pha
2491: where pha.po_header_id = rsl.po_header_id
2492: and pda.po_header_id = pha.po_header_id
2493: and rsl.shipment_line_id = cp_shipment_line_id
2540: WHERE rcv_transaction_id = cp_transaction_id;
2541: /*Bug 8648138*/
2542: /*Added by Wenqiong for bug 14743050 begin*/
2543: CURSOR c_receive_correct_qty(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2544: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
2545: IS
2546: SELECT SUM(quantity)
2547: FROM JAI_RCV_TRANSACTIONS
2548: WHERE shipment_header_id = cp_shipment_header_id
2553: AND parent_transaction_type = 'RECEIVE')
2554: );
2555:
2556: CURSOR c_rtv_correct_qty(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2557: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
2558: IS
2559: SELECT SUM(quantity)
2560: FROM JAI_RCV_TRANSACTIONS
2561: WHERE shipment_header_id = cp_shipment_header_id
2890: p_regime_regno IN VARCHAR2 DEFAULT NULL,
2891: p_organization_id IN hr_all_organization_units.organization_id%TYPE DEFAULT NULL,
2892: p_location_id IN hr_locations_all.location_id%TYPE DEFAULT NULL,
2893: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
2894: p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
2895: p_batch_id IN JAI_RCV_RGM_LINES.BATCH_NUM%TYPE DEFAULT NULL,
2896: p_force IN VARCHAR2 DEFAULT NULL,
2897: p_commit_switch IN VARCHAR2,
2898: p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE,
2997: END process_batch;
2998:
2999: PROCEDURE do_rtv_accounting(
3000: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
3001: p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
3002: p_transaction_id IN rcv_transactions.transaction_id%TYPE,
3003: p_called_from IN VARCHAR2,
3004: p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE,
3005: p_invoice_date IN JAI_RCV_TRANSACTIONS.vat_invoice_date%TYPE,
3022: b.receipt_num,
3023: a.tax_type;
3024:
3025: CURSOR c_min_installment_no(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
3026: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
3027: cp_transaction_type jai_rcv_rgm_claims.transaction_type%type)
3028: IS
3029: SELECT NVL(max(installment_no),0) installment_no --for Bug #4279050, changed from min to max
3030: FROM jai_rcv_rgm_claims
3033: AND transaction_type = cp_transaction_type --'RECEIVE' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3034: AND claimed_date IS NOT NULL;
3035:
3036: CURSOR c_total_reversal_amount(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
3037: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
3038: cp_transaction_id IN rcv_transactions.transaction_id%TYPE,
3039: cp_tax_type IN VARCHAR2,
3040: cp_installment_no IN NUMBER)
3041: IS
3061: --WHERE regime_id = NVL(cp_regime_id, regime_id)
3062: --AND regime_code = NVL(cp_regime_code, regime_code);
3063:
3064: CURSOR c_claim_schedule(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
3065: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
3066: cp_transaction_id IN rcv_transactions.transaction_id%TYPE,
3067: cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE)
3068: IS
3069: SELECT MIN(claim_schedule_id) claim_schedule_id
3546:
3547: -- Added by JMEENA for bug#8302581
3548: Cursor get_item_id(p_transaction_id NUMBER) IS
3549: SELECT item_id
3550: FROM rcv_shipment_lines rsl,
3551: rcv_transactions rt
3552: WHERE rsl.shipment_line_id = rt.shipment_line_id
3553: AND rt.transaction_id = p_transaction_id;
3554: