19: -- current parent condition calling object called object
20: -- level level
21: ------------------------------------------------------------------------------------------------------------------------------------------
22: -- 1 1 insert or trigger calc_taxes_for_invoices
23: -- update on pa_draft_invoices_all
24: --
25: -- 2 1 when request is not null calc_taxes_for_invoices dflt_taxes_for_invoice_lines
26: -- (work in loop for multi invoices)
27: --
84: -------------------------------------------------------------------------------------------------------------
85:
86: type global_type is record
87: (
88: lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type ,
89: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type ,
90: ln_customer_id pa_draft_invoices_all.customer_id%type ,
91: ln_bill_to_customer_id pa_draft_invoices_all.bill_to_customer_id%type ,
92: ln_ship_to_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
85:
86: type global_type is record
87: (
88: lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type ,
89: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type ,
90: ln_customer_id pa_draft_invoices_all.customer_id%type ,
91: ln_bill_to_customer_id pa_draft_invoices_all.bill_to_customer_id%type ,
92: ln_ship_to_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
93: ln_bill_to_address_id pa_draft_invoices_all.bill_to_address_id%type ,
86: type global_type is record
87: (
88: lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type ,
89: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type ,
90: ln_customer_id pa_draft_invoices_all.customer_id%type ,
91: ln_bill_to_customer_id pa_draft_invoices_all.bill_to_customer_id%type ,
92: ln_ship_to_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
93: ln_bill_to_address_id pa_draft_invoices_all.bill_to_address_id%type ,
94: ln_ship_to_address_id pa_draft_invoices_all.ship_to_address_id%type ,
87: (
88: lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type ,
89: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type ,
90: ln_customer_id pa_draft_invoices_all.customer_id%type ,
91: ln_bill_to_customer_id pa_draft_invoices_all.bill_to_customer_id%type ,
92: ln_ship_to_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
93: ln_bill_to_address_id pa_draft_invoices_all.bill_to_address_id%type ,
94: ln_ship_to_address_id pa_draft_invoices_all.ship_to_address_id%type ,
95: -- for credit memo ( invoice ) variables
88: lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type ,
89: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type ,
90: ln_customer_id pa_draft_invoices_all.customer_id%type ,
91: ln_bill_to_customer_id pa_draft_invoices_all.bill_to_customer_id%type ,
92: ln_ship_to_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
93: ln_bill_to_address_id pa_draft_invoices_all.bill_to_address_id%type ,
94: ln_ship_to_address_id pa_draft_invoices_all.ship_to_address_id%type ,
95: -- for credit memo ( invoice ) variables
96: ln_draft_invoice_num_credited pa_draft_invoices_all.draft_invoice_num_credited%type ,
89: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type ,
90: ln_customer_id pa_draft_invoices_all.customer_id%type ,
91: ln_bill_to_customer_id pa_draft_invoices_all.bill_to_customer_id%type ,
92: ln_ship_to_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
93: ln_bill_to_address_id pa_draft_invoices_all.bill_to_address_id%type ,
94: ln_ship_to_address_id pa_draft_invoices_all.ship_to_address_id%type ,
95: -- for credit memo ( invoice ) variables
96: ln_draft_invoice_num_credited pa_draft_invoices_all.draft_invoice_num_credited%type ,
97: ln_write_off_flag pa_draft_invoices_all.write_off_flag%type ,
90: ln_customer_id pa_draft_invoices_all.customer_id%type ,
91: ln_bill_to_customer_id pa_draft_invoices_all.bill_to_customer_id%type ,
92: ln_ship_to_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
93: ln_bill_to_address_id pa_draft_invoices_all.bill_to_address_id%type ,
94: ln_ship_to_address_id pa_draft_invoices_all.ship_to_address_id%type ,
95: -- for credit memo ( invoice ) variables
96: ln_draft_invoice_num_credited pa_draft_invoices_all.draft_invoice_num_credited%type ,
97: ln_write_off_flag pa_draft_invoices_all.write_off_flag%type ,
98: ln_draft_invoice_line_id jai_pa_draft_invoice_lines.draft_invoice_line_id%type,
92: ln_ship_to_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
93: ln_bill_to_address_id pa_draft_invoices_all.bill_to_address_id%type ,
94: ln_ship_to_address_id pa_draft_invoices_all.ship_to_address_id%type ,
95: -- for credit memo ( invoice ) variables
96: ln_draft_invoice_num_credited pa_draft_invoices_all.draft_invoice_num_credited%type ,
97: ln_write_off_flag pa_draft_invoices_all.write_off_flag%type ,
98: ln_draft_invoice_line_id jai_pa_draft_invoice_lines.draft_invoice_line_id%type,
99: ln_draft_invoice_id jai_pa_draft_invoice_lines.draft_invoice_id%type ,
100: ln_line_amt jai_pa_draft_invoice_lines.line_amt%type
93: ln_bill_to_address_id pa_draft_invoices_all.bill_to_address_id%type ,
94: ln_ship_to_address_id pa_draft_invoices_all.ship_to_address_id%type ,
95: -- for credit memo ( invoice ) variables
96: ln_draft_invoice_num_credited pa_draft_invoices_all.draft_invoice_num_credited%type ,
97: ln_write_off_flag pa_draft_invoices_all.write_off_flag%type ,
98: ln_draft_invoice_line_id jai_pa_draft_invoice_lines.draft_invoice_line_id%type,
99: ln_draft_invoice_id jai_pa_draft_invoice_lines.draft_invoice_id%type ,
100: ln_line_amt jai_pa_draft_invoice_lines.line_amt%type
101: ) ;
107: procedure calc_taxes_for_invoices
108: (
109: err_buf out nocopy varchar2 ,
110: retcode out nocopy varchar2 ,
111: pn_request_id in pa_draft_invoices_all.request_id%type ,
112: pn_project_id in pa_draft_invoices_all.project_id%type ,
113: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
114: pv_event in varchar2
115: ) is
108: (
109: err_buf out nocopy varchar2 ,
110: retcode out nocopy varchar2 ,
111: pn_request_id in pa_draft_invoices_all.request_id%type ,
112: pn_project_id in pa_draft_invoices_all.project_id%type ,
113: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
114: pv_event in varchar2
115: ) is
116:
109: err_buf out nocopy varchar2 ,
110: retcode out nocopy varchar2 ,
111: pn_request_id in pa_draft_invoices_all.request_id%type ,
112: pn_project_id in pa_draft_invoices_all.project_id%type ,
113: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
114: pv_event in varchar2
115: ) is
116:
117: -- cursor is responsible for getting range of invoices
114: pv_event in varchar2
115: ) is
116:
117: -- cursor is responsible for getting range of invoices
118: lr_get_invoices pa_draft_invoices_all%rowtype ;
119: lv_process_flag varchar2(30);
120: lv_process_message varchar2(2000);
121: v_parent_request_id NUMBER;
122: req_status BOOLEAN := TRUE;
127: v_message VARCHAR2(100);
128:
129: -- cursor for getting invoices detail depends upon request id
130: cursor cur_get_invoices is
131: select pa_draft_invoices_all.*
132: from pa_draft_invoices_all
133: where request_id = pn_request_id ;
134:
135: -- cursor for getting one single invoice detail for corresponding project id and draft invoice num
128:
129: -- cursor for getting invoices detail depends upon request id
130: cursor cur_get_invoices is
131: select pa_draft_invoices_all.*
132: from pa_draft_invoices_all
133: where request_id = pn_request_id ;
134:
135: -- cursor for getting one single invoice detail for corresponding project id and draft invoice num
136:
134:
135: -- cursor for getting one single invoice detail for corresponding project id and draft invoice num
136:
137: Cursor cur_get_inv_detail is
138: select pa_draft_invoices_all.*
139: from pa_draft_invoices_all
140: where project_id = pn_project_id and
141: draft_invoice_num = pn_draft_invoice_num ;
142:
135: -- cursor for getting one single invoice detail for corresponding project id and draft invoice num
136:
137: Cursor cur_get_inv_detail is
138: select pa_draft_invoices_all.*
139: from pa_draft_invoices_all
140: where project_id = pn_project_id and
141: draft_invoice_num = pn_draft_invoice_num ;
142:
143: begin
191: /*-------------------------------begin local method dflt_taxes_for_invoice_lines -----------------------------*/
192:
193:
194: procedure dflt_taxes_for_invoice_lines(
195: r_new in pa_draft_invoices_all%rowtype,
196: pv_action in varchar2,
197: pv_process_message out nocopy varchar2,
198: pv_process_flag out nocopy varchar2
199: ) is
334:
335: /*-------------------------------BEGIN LOCAL METHOD GET_TAX_CATEGORY -----------------------------*/
336:
337: procedure get_tax_category(
338: pn_project_id in pa_draft_invoices_all.project_id%type ,
339: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
340: pn_line_num in pa_draft_invoice_items.line_num%type ,
341: pn_event_task_id in pa_draft_invoice_items.event_task_id%type,
342: pn_event_num in pa_draft_invoice_items.event_num%type,
335: /*-------------------------------BEGIN LOCAL METHOD GET_TAX_CATEGORY -----------------------------*/
336:
337: procedure get_tax_category(
338: pn_project_id in pa_draft_invoices_all.project_id%type ,
339: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
340: pn_line_num in pa_draft_invoice_items.line_num%type ,
341: pn_event_task_id in pa_draft_invoice_items.event_task_id%type,
342: pn_event_num in pa_draft_invoice_items.event_num%type,
343: pv_action in varchar2,
462: pv_process_flag := jai_constants.expected_error;
463: end get_tax_category ;
464: /*-------------------------------BEGIN LOCAL METHOD GET_EVENT_TAX_CATEGORY -----------------------------*/
465: procedure get_event_tax_category(
466: pn_project_id in pa_draft_invoices_all.project_id%type ,
467: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
468: pn_line_num in pa_draft_invoice_items.line_num%type ,
469: pn_event_task_id in pa_draft_invoice_items.event_task_id%type,
470: pn_event_num in pa_draft_invoice_items.event_num%type,
463: end get_tax_category ;
464: /*-------------------------------BEGIN LOCAL METHOD GET_EVENT_TAX_CATEGORY -----------------------------*/
465: procedure get_event_tax_category(
466: pn_project_id in pa_draft_invoices_all.project_id%type ,
467: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
468: pn_line_num in pa_draft_invoice_items.line_num%type ,
469: pn_event_task_id in pa_draft_invoice_items.event_task_id%type,
470: pn_event_num in pa_draft_invoice_items.event_num%type,
471: pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
573: end get_event_tax_category ;
574:
575: /*-------------------------------begin local method get_project_tax_category -----------------------------*/
576: procedure get_project_tax_category
577: ( pn_project_id in pa_draft_invoices_all.project_id%type ,
578: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
579: pn_line_num in pa_draft_invoice_items.line_num%type ,
580: pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
581: pv_process_status out nocopy varchar2 ,
574:
575: /*-------------------------------begin local method get_project_tax_category -----------------------------*/
576: procedure get_project_tax_category
577: ( pn_project_id in pa_draft_invoices_all.project_id%type ,
578: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
579: pn_line_num in pa_draft_invoice_items.line_num%type ,
580: pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
581: pv_process_status out nocopy varchar2 ,
582: pv_process_message out nocopy varchar2,
622: pv_process_flag := jai_constants.unexpected_error;
623: end get_project_tax_category;
624: /*-------------------------------begin local method get_cust_tax_category -----------------------------*/
625: procedure get_cust_tax_category
626: ( pn_project_id in pa_draft_invoices_all.project_id%type ,
627: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
628: pn_line_num in pa_draft_invoice_items.line_num%type ,
629: pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
630: pv_process_status out nocopy varchar2 ,
623: end get_project_tax_category;
624: /*-------------------------------begin local method get_cust_tax_category -----------------------------*/
625: procedure get_cust_tax_category
626: ( pn_project_id in pa_draft_invoices_all.project_id%type ,
627: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
628: pn_line_num in pa_draft_invoice_items.line_num%type ,
629: pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
630: pv_process_status out nocopy varchar2 ,
631: pv_process_message out nocopy varchar2,
632: pv_process_flag out nocopy varchar2
633: ) is
634: ----------------variable declarations---------------------------
635: ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type;
636: ln_tax_customer_id pa_draft_invoices_all.ship_to_customer_id%type ;
637: ln_tax_address_id pa_draft_invoices_all.ship_to_address_id%type ;
638: ----------------cursor declarations------------------------------
639: -- responsible for getting tax category attached with customer / site combination or customer / null site combination
640: cursor cur_cust_site_tax(cn_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
633: ) is
634: ----------------variable declarations---------------------------
635: ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type;
636: ln_tax_customer_id pa_draft_invoices_all.ship_to_customer_id%type ;
637: ln_tax_address_id pa_draft_invoices_all.ship_to_address_id%type ;
638: ----------------cursor declarations------------------------------
639: -- responsible for getting tax category attached with customer / site combination or customer / null site combination
640: cursor cur_cust_site_tax(cn_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
641: cn_address_id pa_draft_invoices_all.ship_to_address_id%type) is
636: ln_tax_customer_id pa_draft_invoices_all.ship_to_customer_id%type ;
637: ln_tax_address_id pa_draft_invoices_all.ship_to_address_id%type ;
638: ----------------cursor declarations------------------------------
639: -- responsible for getting tax category attached with customer / site combination or customer / null site combination
640: cursor cur_cust_site_tax(cn_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
641: cn_address_id pa_draft_invoices_all.ship_to_address_id%type) is
642: select setup_value1
643: from jai_pa_setup_values
644: where attribute1 = cn_customer_id and
637: ln_tax_address_id pa_draft_invoices_all.ship_to_address_id%type ;
638: ----------------cursor declarations------------------------------
639: -- responsible for getting tax category attached with customer / site combination or customer / null site combination
640: cursor cur_cust_site_tax(cn_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
641: cn_address_id pa_draft_invoices_all.ship_to_address_id%type) is
642: select setup_value1
643: from jai_pa_setup_values
644: where attribute1 = cn_customer_id and
645: attribute2 = cn_address_id and
644: where attribute1 = cn_customer_id and
645: attribute2 = cn_address_id and
646: context = jai_constants.setup_customer_site ;
647:
648: cursor cur_cust_tax(cn_customer_id pa_draft_invoices_all.ship_to_customer_id%type) is
649: select setup_value1
650: from jai_pa_setup_values
651: where attribute1 = cn_customer_id and
652: context = jai_constants.setup_customer_site and
699: end get_cust_tax_category;
700:
701: /*-------------------------------BEGIN LOCAL METHOD GET_EXPN_TAX_CATEGORY -----------------------------*/
702: procedure get_expn_tax_category
703: ( pn_project_id in pa_draft_invoices_all.project_id%type ,
704: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
705: pn_line_num in pa_draft_invoice_items.line_num%type ,
706: pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
707: pv_process_status out nocopy varchar2 ,
700:
701: /*-------------------------------BEGIN LOCAL METHOD GET_EXPN_TAX_CATEGORY -----------------------------*/
702: procedure get_expn_tax_category
703: ( pn_project_id in pa_draft_invoices_all.project_id%type ,
704: pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
705: pn_line_num in pa_draft_invoice_items.line_num%type ,
706: pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
707: pv_process_status out nocopy varchar2 ,
708: pv_process_message out nocopy varchar2,
993:
994: ----------------variable declarations---------------------------
995: ln_tax_amount number ;
996: ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ;
997: lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type;
998: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type;
999: ----------------------------------------------------------------
1000: -- responsible to check for tax category id whether it is already exists .
1001: cursor cur_get_tax_caegotry is
994: ----------------variable declarations---------------------------
995: ln_tax_amount number ;
996: ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ;
997: lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type;
998: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type;
999: ----------------------------------------------------------------
1000: -- responsible to check for tax category id whether it is already exists .
1001: cursor cur_get_tax_caegotry is
1002: select tax_category_id
1006: source_doc_type = jai_constants.pa_draft_invoice ;
1007:
1008: cursor cur_get_inv_info is
1009: select pdia.inv_currency_code , pdia.inv_exchange_rate
1010: from pa_draft_invoices_all pdia, jai_pa_draft_invoice_lines jpdil
1011: where pdia.project_id = jpdil.project_id and
1012: pdia.draft_invoice_num = jpdil.draft_invoice_num and
1013: jpdil.draft_invoice_line_id = pn_draft_invoice_line_id and
1014: draft_invoice_id = pn_draft_invoice_id ;
1138: end if ;
1139:
1140: IF PV_CALLED_FROM IS NOT NULL AND PV_CALLED_FROM = 'JAINRWDI' THEN
1141: -- procedure is being called from invoice review - india ui hence get currency related
1142: -- information from the header table (pa_draft_invoices_all).
1143: --
1144: -- if procedure is being called from trigger this control should never come here
1145: -- it will result in mutation
1146: open cur_get_inv_info;
1249: bill_to_address_id,
1250: ship_to_address_id,
1251: draft_invoice_num_credited,
1252: write_off_flag
1253: from pa_draft_invoices_all
1254: where project_id = pn_project_id and
1255: draft_invoice_num = pn_draft_invoice_num ;
1256:
1257: begin
1285: pv_process_message out nocopy varchar2,
1286: pv_process_flag out nocopy varchar2
1287: ) is
1288:
1289: lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type;
1290: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type;
1291: ln_tax_amount number ;
1292:
1293: cursor cur_get_line_detail is
1286: pv_process_flag out nocopy varchar2
1287: ) is
1288:
1289: lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type;
1290: ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type;
1291: ln_tax_amount number ;
1292:
1293: cursor cur_get_line_detail is
1294: select draft_invoice_id , draft_invoice_line_id