[Home] [Help]
54: PROCEDURE revert_negative_journals IS
55:
56: cursor journals_with_negative_amts is
57: select distinct field6
58: from jl_br_sped_extr_data_t a
59: where request_id = g_concurrent_request_id
60: and field1='I250'
61: and not exists (select 1
62: from jl_br_sped_extr_data_t
58: from jl_br_sped_extr_data_t a
59: where request_id = g_concurrent_request_id
60: and field1='I250'
61: and not exists (select 1
62: from jl_br_sped_extr_data_t
63: where request_id = g_concurrent_request_id
64: and field4 like '%####%'
65: and field6 = a.field6
66: and field1='I250')
67: and substr(field4,1,1) = '-';
68:
69: cursor accts_with_negative_amts is
70: select distinct field2 na ,field3 cc
71: from jl_br_sped_extr_data_t a
72: where field1 ='I250'
73: and request_id = g_concurrent_request_id
74: and not exists (select 1
75: from jl_br_sped_extr_data_t
71: from jl_br_sped_extr_data_t a
72: where field1 ='I250'
73: and request_id = g_concurrent_request_id
74: and not exists (select 1
75: from jl_br_sped_extr_data_t
76: where request_id = g_concurrent_request_id
77: and field1 = 'I250'
78: and field4 like '%####%'
79: and field2 = a.field2
129: --fnd_file.put_line(fnd_file.log,'in revert negative journals - After accts fetch');
130:
131: BEGIN
132:
133: update jl_br_sped_extr_data_t a
134: set field4 = trim(to_char(abs(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),
135: field5 = decode(field5,'C','D','D','C')
136: where field1 = 'I250'
137: and substr(field4,1,1)='-'
136: where field1 = 'I250'
137: and substr(field4,1,1)='-'
138: and request_id = g_concurrent_request_id
139: and not exists (select 1
140: from jl_br_sped_extr_data_t b
141: where a.field6 = b.field6
142: and b.field1 = 'I250'
143: and b.request_id = g_concurrent_request_id
144: and b.field4 like '%###%');
146:
147: --fnd_file.put_line(fnd_file.log,'in revert negative journals - After I250 update');
148: forall i in journals_tab.first .. journals_tab.last
149:
150: update jl_br_sped_extr_data_t a -- need to verify
151: set field4 = (select trim(to_char(sum(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
152: from jl_br_sped_extr_data_t b
153: where b.field1 ='I250'
154: and b.field6 = journals_tab(i)
148: forall i in journals_tab.first .. journals_tab.last
149:
150: update jl_br_sped_extr_data_t a -- need to verify
151: set field4 = (select trim(to_char(sum(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
152: from jl_br_sped_extr_data_t b
153: where b.field1 ='I250'
154: and b.field6 = journals_tab(i)
155: and b.field5 = 'D'
156: and b.request_id = g_concurrent_request_id)
160:
161: --fnd_file.put_line(fnd_file.log,'in revert negative journals - After I200 update');
162: forall i in naturalaccts_tab.first .. naturalaccts_tab.last
163:
164: update jl_br_sped_extr_data_t a
165: set field6 = (select trim(to_char(nvl(sum(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),0),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
166: from jl_br_sped_extr_data_t b
167: where b.field1 = 'I250'
168: and b.field5 = 'D'
162: forall i in naturalaccts_tab.first .. naturalaccts_tab.last
163:
164: update jl_br_sped_extr_data_t a
165: set field6 = (select trim(to_char(nvl(sum(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),0),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
166: from jl_br_sped_extr_data_t b
167: where b.field1 = 'I250'
168: and b.field5 = 'D'
169: and b.field2 = naturalaccts_tab(i)
170: and b.field3 = costcenters_tab(i)
169: and b.field2 = naturalaccts_tab(i)
170: and b.field3 = costcenters_tab(i)
171: and b.request_id = g_concurrent_request_id),
172: field7 =(select trim(to_char(nvl(sum(to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''')),0),'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
173: from jl_br_sped_extr_data_t c
174: where c.field1 = 'I250'
175: and c.field5 = 'C'
176: and c.field2 = naturalaccts_tab(i)
177: and c.field3 = costcenters_tab(i)
1858:
1859: --insert company info into 0000 register.
1860: BEGIN
1861:
1862: INSERT INTO jl_br_sped_extr_data_t
1863: (request_id,
1864: block,
1865: record_seq,
1866: field1,
1891: last_update_date,
1892: last_update_login)
1893: VALUES (g_concurrent_request_id
1894: ,'0' -- Block
1895: ,jl_br_sped_extr_data_t_s.nextval --record_seq
1896: ,'0000' -- Register (field 1)
1897: ,'|'
1898: ,'LECD' -- Fixed Text (field 2)
1899: ,'|'
1953: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
1954: G_PKG_NAME||': '||l_api_name||'()+');
1955: END IF;
1956:
1957: INSERT INTO jl_br_sped_extr_data_t
1958: (request_id,
1959: block,
1960: record_seq,
1961: field1,
1968: last_update_date,
1969: last_update_login)
1970: VALUES( g_concurrent_request_id,
1971: '0', --block
1972: jl_br_sped_extr_data_t_s.nextval, --record_seq
1973: '0001', --Register (field1)
1974: '|',
1975: 0,-- null,--decode(count(*),0,1,0), --field2
1976: '|'
2014: END IF;
2015:
2016: IF l_estb_acts_as_company = 'N' THEN --LE acts as company
2017:
2018: INSERT INTO jl_br_sped_extr_data_t
2019: (request_id,
2020: block,
2021: record_seq,
2022: field1,
2032: last_update_login
2033: )
2034: SELECT g_concurrent_request_id
2035: ,'0' -- Block
2036: ,jl_br_sped_extr_data_t_s.nextval --record_seq
2037: ,'0007' -- Register (field 1)
2038: ,'|'
2039: ,reg.reg_information1 --field 2
2040: ,'|'
2053: AND (nvl(effective_to,sysdate) >= g_end_date OR effective_to IS NULL); -- Registration should be there for entire period.
2054:
2055: ELSE --Establishment acts as company
2056:
2057: INSERT INTO jl_br_sped_extr_data_t
2058: (request_id,
2059: block,
2060: record_seq,
2061: field1,
2071: last_update_login
2072: )
2073: SELECT g_concurrent_request_id
2074: ,'0' -- Block
2075: ,jl_br_sped_extr_data_t_s.nextval --record_seq
2076: ,'0007' -- Register (field 1)
2077: ,'|'
2078: ,reg.reg_information1 --field 2
2079: ,'|'
2383: END IF; -- END for p_insciption_source='XLE'
2384:
2385: BEGIN
2386:
2387: INSERT INTO jl_br_sped_extr_data_t
2388: (request_id,
2389: block,
2390: record_seq,
2391: field1,
2411: last_update_login
2412: )
2413: VALUES(g_concurrent_request_id
2414: ,'0' -- Block
2415: ,jl_br_sped_extr_data_t_s.nextval --record_seq
2416: ,'0020' -- Register (field 1)
2417: ,'|'
2418: ,1 --LE
2419: ,'|'
2666: END IF; -- END for p_insciption_source='XLE'
2667:
2668: BEGIN
2669:
2670: INSERT INTO jl_br_sped_extr_data_t
2671: (request_id,
2672: block,
2673: record_seq,
2674: field1,
2694: last_update_login
2695: )
2696: VALUES(g_concurrent_request_id
2697: ,'0' -- Block
2698: ,jl_br_sped_extr_data_t_s.nextval --record_seq
2699: ,'0020' -- Register (field 1)
2700: ,'|'
2701: ,0 --Establishment
2702: ,'|'
2790: END IF;
2791:
2792: For partic_record in c_participants LOOP
2793:
2794: INSERT INTO jl_br_sped_extr_data_t
2795: (request_id,
2796: block,
2797: record_seq,
2798: field1,
2827: last_update_date,
2828: last_update_login)
2829: values (g_concurrent_request_id
2830: ,'0' -- Block
2831: ,jl_br_sped_extr_data_t_s.nextval -- Record_seq
2832: ,'0150' -- Register (field 1)
2833: ,'|'
2834: , partic_record.participant_code --field 2
2835: ,'|'
2861: ,g_last_update_date
2862: ,g_last_update_login );
2863:
2864:
2865: INSERT INTO jl_br_sped_extr_data_t
2866: (request_id,
2867: block,
2868: record_seq,
2869: field1,
2880: last_update_date,
2881: last_update_login)
2882: SELECT g_concurrent_request_id
2883: ,'0' -- Block
2884: ,jl_br_sped_extr_data_t_s.nextval -- Record_seq
2885: ,'0180' -- Register (field 1)
2886: ,'|'
2887: ,relationship_code --field 2
2888: ,'|'
2905:
2906: END LOOP;
2907:
2908: SELECT COUNT(*) INTO l_count
2909: FROM jl_br_sped_extr_data_t
2910: WHERE request_id =g_concurrent_request_id
2911: AND block = '0'
2912: AND field1 = '0150';
2913:
2913:
2914: -- FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'(): 0150 Records Inserted : '||l_count);
2915:
2916: SELECT COUNT(*) INTO l_count
2917: FROM jl_br_sped_extr_data_t
2918: WHERE request_id =g_concurrent_request_id
2919: AND block = '0'
2920: AND field1 = '0180';
2921: -- FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'(): 0180 Records Inserted : '||l_count);
2949: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2950: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
2951: G_PKG_NAME||': '||l_api_name||'()+');
2952: END IF;
2953: INSERT INTO jl_br_sped_extr_data_t
2954: (request_id,
2955: block,
2956: record_seq,
2957: field1,
2965: last_update_login
2966: )
2967: VALUES( g_concurrent_request_id
2968: ,'0' -- Block
2969: ,jl_br_sped_extr_data_t_s.nextval -- Record_seq
2970: ,'0990' -- Register (field 1)
2971: ,'|'
2972: ,0--,null --count(*) -- Field 2
2973: ,'|'
3006: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
3007: G_PKG_NAME||': '||l_api_name||'()+');
3008: END IF;
3009:
3010: INSERT INTO jl_br_sped_extr_data_t
3011: (request_id,
3012: block,
3013: record_seq,
3014: field1,
3021: last_update_date,
3022: last_update_login)
3023: VALUES( g_concurrent_request_id,
3024: 'I', --block
3025: jl_br_sped_extr_data_t_s.nextval, --Record_seq
3026: 'I001', --Register (field1)
3027: '|',
3028: 0,--null,--decode(count(*),0,1,0), --field2
3029: '|'
3063: G_PKG_NAME||': '||l_api_name||'()+');
3064: END IF;
3065: -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'G_BOOKKEEPING_TYPE: ' ||g_bookkeeping_type);
3066:
3067: INSERT INTO jl_br_sped_extr_data_t
3068: (request_id,
3069: block,
3070: record_seq,
3071: field1,
3081: last_update_login)
3082: VALUES
3083: ( g_concurrent_request_id
3084: ,'I' -- Block
3085: ,jl_br_sped_extr_data_t_s.nextval -- Record_seq
3086: ,'I010' -- Register (field 1)
3087: ,'|'
3088: ,substr(g_bookkeeping_type,1,1) -- field 2
3089: ,'|'
3128: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
3129: G_PKG_NAME||': '||l_api_name||'()+');
3130: END IF;
3131:
3132: INSERT INTO jl_br_sped_extr_data_t
3133: (request_id,
3134: block,
3135: record_seq,
3136: field1,
3150: last_update_login)
3151: SELECT
3152: g_concurrent_request_id
3153: ,'I' -- Block
3154: ,jl_br_sped_extr_data_t_s.nextval -- Record_seq
3155: ,'I012' -- Register (field 1)
3156: ,'|'
3157: ,book_number -- field 2
3158: ,'|'
3217: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
3218: G_PKG_NAME||': '||l_api_name||'()+');
3219: END IF;
3220:
3221: l_query := 'INSERT INTO jl_br_sped_extr_data_t
3222: (request_id,
3223: block,
3224: record_seq,
3225: field1,
3233: last_update_login
3234: )
3235: SELECT '||g_concurrent_request_id|| '
3236: ,''I'' -- Block
3237: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
3238: ,''I015'' -- Register (field 1)
3239: ,''|''
3240: ,natural_acct
3241: ,''|''
3647:
3648: -- inserting data into data extract table
3649: BEGIN
3650:
3651: INSERT INTO jl_br_sped_extr_data_t
3652: (request_id,
3653: block,
3654: record_seq,
3655: field1,
3680: last_update_date,
3681: last_update_login)
3682: VALUES(g_concurrent_request_id
3683: ,'I' -- Block
3684: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
3685: ,'I030' -- Register (field 1)
3686: ,'|'
3687: ,'TERMO DE ABERTURA' -- Fixed Text (field 2)
3688: ,'|'
3933: return;
3934: END;
3935:
3936:
3937: INSERT INTO jl_br_sped_extr_data_t
3938: (request_id,
3939: block,
3940: record_seq,
3941: field1,
3960: last_update_date,
3961: last_update_login)
3962: VALUES( g_concurrent_request_id
3963: ,'I' -- Block
3964: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
3965: ,'I050' -- Register (field 1)
3966: ,'|'
3967: , to_char(l_fv_last_update_date,'DDMMYYYY') --field 2
3968: ,'|'
3987:
3988: END IF; -- IF fv_parent_value_level(i) = 1 and l_prev_level1_parent <> fv_parent_value(i) THEN
3989:
3990:
3991: INSERT INTO jl_br_sped_extr_data_t
3992: (request_id,
3993: block,
3994: record_seq,
3995: field1,
4014: last_update_date,
4015: last_update_login)
4016: VALUES( g_concurrent_request_id
4017: ,'I' -- Block
4018: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
4019: ,'I050' -- Register (field 1)
4020: ,'|'
4021: , to_char(fv_last_update_date(i),'DDMMYYYY') --field 2
4022: ,'|'
4202: END;
4203:
4204: IF l_referential_account IS NOT NULL THEN
4205:
4206: INSERT INTO jl_br_sped_extr_data_t
4207: (request_id,
4208: block,
4209: record_seq,
4210: field1,
4221: last_update_date,
4222: last_update_login)
4223: VALUES (g_concurrent_request_id
4224: ,'I' -- Block
4225: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
4226: ,'I051' -- Register (field 1)
4227: ,'|'
4228: , SUBSTR(l_institution_resp_code,1,2) --field 2
4229: ,'|'
4276: END IF;
4277:
4278:
4279: /* Inserts all agglutination_codes defined for balance_statement_report_id*/
4280: sql_stmt := 'INSERT INTO jl_br_sped_extr_data_t
4281: (request_id,
4282: block,
4283: record_seq,
4284: field1,
4294: last_update_login
4295: )
4296: SELECT '||g_concurrent_request_id||'
4297: ,''I'' -- Block
4298: ,jl_br_sped_extr_data_t_s.nextval
4299: ,''I052'' -- Register (field 1)
4300: ,''|''
4301: , NULL --field 2
4302: ,''|''
4326: EXECUTE IMMEDIATE sql_stmt using p_account_flex_value,p_account_flex_value;
4327:
4328:
4329: /* Inserts all agglutination_codes defined for income_statement_report_id*/
4330: sql_stmt := 'INSERT INTO jl_br_sped_extr_data_t
4331: (request_id,
4332: block,
4333: record_seq,
4334: field1,
4344: last_update_login
4345: )
4346: SELECT '||g_concurrent_request_id||'
4347: ,''I'' -- Block
4348: ,jl_br_sped_extr_data_t_s.nextval
4349: ,''I052'' -- Register (field 1)
4350: ,''|''
4351: , NULL --field 2
4352: ,''|''
4413: RETURN;
4414:
4415: END IF;
4416:
4417: l_query := 'INSERT INTO jl_br_sped_extr_data_t
4418: (request_id,
4419: block,
4420: record_seq,
4421: field1,
4428: last_update_login
4429: )
4430: SELECT '||g_concurrent_request_id||
4431: ',''I'' -- Block
4432: ,jl_br_sped_extr_data_t_s.nextval --record_seq
4433: ,''I100'' -- Register (field 1)
4434: ,''|''
4435: ,to_char(fv.last_update_date,''DDMMYYYY'')
4436: ,''|''
4487: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
4488: G_PKG_NAME||': ' ||l_api_name||'()+');
4489: END IF;
4490:
4491: INSERT INTO jl_br_sped_extr_data_t
4492: (request_id,
4493: block,
4494: record_seq,
4495: field1,
4505: last_update_login
4506: )
4507: VALUES (g_concurrent_request_id
4508: ,'I' -- Block
4509: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
4510: ,'I150' -- Register (field 1)
4511: ,'|'
4512: ,to_char(g_start_date,'DDMMYYYY') --filed2
4513: ,'|'
4551: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
4552: G_PKG_NAME||': ' ||l_api_name||'()+');
4553: END IF;
4554:
4555: INSERT INTO jl_br_sped_extr_data_t
4556: (request_id,
4557: block,
4558: record_seq,
4559: field1,
4566: last_update_login
4567: )
4568: VALUES (g_concurrent_request_id
4569: ,'I' -- Block
4570: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
4571: ,'I151' -- Register (field 1)
4572: ,'|'
4573: ,'|'
4574: ,g_created_by
4612: END IF;
4613:
4614:
4615: l_query1 :=
4616: 'INSERT INTO jl_br_sped_extr_data_t
4617: (request_id,
4618: block,
4619: record_seq,
4620: field1,
4642: last_update_login
4643: )
4644: SELECT '||g_concurrent_request_id||
4645: ',''I'' -- Block
4646: ,jl_br_sped_extr_data_t_s.nextval --record_seq
4647: ,''I155'' -- Register (field 1)
4648: ,''|''
4649: ,natural_acct
4650: ,''|''
4736:
4737:
4738: -- fnd_file.put_line(fnd_file.log,'I155_query:'||l_query1);
4739:
4740: execute immediate 'INSERT INTO jl_br_sped_extr_data_t
4741: (request_id,
4742: block,
4743: record_seq,
4744: field1,
4766: last_update_login
4767: )
4768: SELECT '||g_concurrent_request_id||
4769: ',''I'' -- Block
4770: ,jl_br_sped_extr_data_t_s.nextval --record_seq
4771: ,''I155'' -- Register (field 1)
4772: ,''|''
4773: ,natural_acct
4774: ,''|''
4941: ELSE
4942: l_jounrnal_flag := 'N';
4943: END IF;
4944:
4945: INSERT INTO jl_br_sped_extr_data_t
4946: (request_id,
4947: block,
4948: record_seq,
4949: field1,
4963: last_update_login
4964: )
4965: VALUES (g_concurrent_request_id
4966: ,'I' -- Block
4967: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
4968: ,'I200' -- field 1
4969: ,'|'
4970: ,c_journal_header.name --field 2
4971: ,'|'
5036: BEGIN
5037:
5038: -- Debit Lines
5039:
5040: INSERT INTO jl_br_sped_extr_data_t
5041: (request_id,
5042: block,
5043: record_seq,
5044: field1,
5066: last_update_login
5067: )
5068: SELECT g_concurrent_request_id
5069: ,'I' -- Block
5070: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
5071: ,'I250' -- field 1
5072: ,'|'
5073: ,get_segment_value(jl.code_combination_id,g_account_segment) natural_Acct
5074: ,'|'
5099:
5100:
5101: -- Credit Lines
5102:
5103: INSERT INTO jl_br_sped_extr_data_t
5104: (request_id,
5105: block,
5106: record_seq,
5107: field1,
5129: last_update_login
5130: )
5131: SELECT g_concurrent_request_id
5132: ,'I' -- Block
5133: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
5134: ,'I250' -- field 1
5135: ,'|'
5136: ,get_segment_value(jl.code_combination_id,g_account_segment) natural_Acct
5137: ,'|'
5173:
5174: --Extract the detail lines by drill down to xla_ae_lines_all and xla_distributions_links table.
5175:
5176: BEGIN
5177: INSERT INTO jl_br_sped_extr_data_t
5178: (request_id,
5179: block,
5180: record_seq,
5181: field1,
5203: last_update_login
5204: )
5205: SELECT g_concurrent_request_id
5206: , 'I' -- Block
5207: , jl_br_sped_extr_data_t_s.nextval -- record_seq
5208: , 'I250' -- field 1
5209: , '|'
5210: , get_segment_value(xll.code_combination_id, g_account_segment)
5211: , '|'
5245: AND xld.UNROUNDED_ACCOUNTED_DR IS NOT NULL
5246: AND (l_exclusive_mode='Y' OR (l_exclusive_mode='N' AND get_segment_value(xll.code_combination_id,g_bsv_segment) in (SELECT jg_info_v1 from jg_zz_vat_trx_gt))); --- Need to modify
5247:
5248: -- inserts credit lines. added as part of ER#11653651
5249: INSERT INTO jl_br_sped_extr_data_t
5250: (request_id,
5251: block,
5252: record_seq,
5253: field1,
5275: last_update_login
5276: )
5277: SELECT g_concurrent_request_id
5278: , 'I' -- Block
5279: , jl_br_sped_extr_data_t_s.nextval -- record_seq
5280: , 'I250' -- field 1
5281: , '|'
5282: , get_segment_value(xll.code_combination_id, g_account_segment)
5283: , '|'
5401: LOOP
5402:
5403: IF effective_date(i) <> l_previous_date THEN
5404:
5405: INSERT INTO jl_br_sped_extr_data_t
5406: (request_id,
5407: block,
5408: record_seq,
5409: field1,
5417: last_update_login
5418: )
5419: VALUES (g_concurrent_request_id
5420: ,'I' -- Block
5421: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
5422: ,'I300' -- field 1
5423: ,'|'
5424: ,to_char(effective_date(i),'DDMMYYYY')
5425: ,'|'
5432:
5433: l_previous_date := effective_date(i);
5434: END IF;
5435:
5436: INSERT INTO jl_br_sped_extr_data_t
5437: (request_id,
5438: block,
5439: record_seq,
5440: field1,
5454: last_update_login
5455: )
5456: VALUES (g_concurrent_request_id
5457: ,'I' -- Block
5458: ,jl_br_sped_extr_data_t_s.nextval --record_seq
5459: ,'I310' -- field 1
5460: ,'|'
5461: ,natural_acct(i)
5462: ,'|'
5513: END IF;
5514:
5515:
5516:
5517: INSERT INTO jl_br_sped_extr_data_t
5518: (request_id,
5519: block,
5520: record_seq,
5521: field1,
5529: last_update_login
5530: )
5531: VALUES (g_concurrent_request_id
5532: ,'I' -- Block
5533: ,jl_br_sped_extr_data_t_s.nextval -- record_seq
5534: ,'I350' -- field 1
5535: ,'|'
5536: ,to_char(g_end_date,'DDMMYYYY')
5537: ,'|'
5586: END IF;
5587:
5588: IF g_adjustment_period_name IS NOT NULL THEN -- It will be true always. so is this check require???
5589:
5590: l_query := 'INSERT INTO jl_br_sped_extr_data_t
5591: (request_id,
5592: block,
5593: record_seq,
5594: field1,
5608: last_update_login
5609: )
5610: SELECT '|| g_concurrent_request_id||
5611: ',''I'' Block
5612: ,jl_br_sped_extr_data_t_s.nextval
5613: ,''I355''
5614: ,''|''
5615: , natural_acct
5616: ,''|''
5686: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
5687: G_PKG_NAME||': '||l_api_name||'()+');
5688: END IF;
5689:
5690: INSERT INTO jl_br_sped_extr_data_t
5691: (request_id,
5692: block,
5693: record_seq,
5694: field1,
5701: last_update_date,
5702: last_update_login)
5703: VALUES( g_concurrent_request_id,
5704: 'I', --block
5705: jl_br_sped_extr_data_t_s.nextval, --record_seq
5706: 'I990', --Register (field1)
5707: '|',
5708: null,--count(*), --field2
5709: '|',
5744: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
5745: G_PKG_NAME||': '||l_api_name||'()+');
5746: END IF;
5747:
5748: INSERT INTO jl_br_sped_extr_data_t
5749: (request_id,
5750: block,
5751: record_seq,
5752: field1,
5759: last_update_date,
5760: last_update_login)
5761: VALUES( g_concurrent_request_id,
5762: 'J', --block
5763: jl_br_sped_extr_data_t_s.nextval, --record_seq
5764: 'J001', --Register (field1)
5765: '|',
5766: 0,-- null, --decode(count(*),0,1,0), --field2
5767: '|',
5813: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
5814: G_PKG_NAME||': '||l_api_name||'()+');
5815: END IF;
5816:
5817: INSERT INTO jl_br_sped_extr_data_t
5818: (request_id,
5819: block,
5820: record_seq,
5821: field1,
5834: last_update_date,
5835: last_update_login)
5836: VALUES( g_concurrent_request_id,
5837: 'J', --block
5838: jl_br_sped_extr_data_t_s.nextval, --record_seq
5839: 'J005', --Register (field1)
5840: '|',
5841: to_char(g_start_date,'ddmmyyyy'), --field2
5842: '|',
5908: acct_cur ref_cursor;
5909: CURSOR J100_recs_by_jcp IS
5910: SELECT field6, -- amount
5911: field8 -- row_sequence
5912: FROM jl_br_sped_extr_data_t
5913: WHERE request_id = g_concurrent_request_id
5914: AND field1 = 'J100'
5915: ORDER BY record_seq;
5916:
5915: ORDER BY record_seq;
5916:
5917: CURSOR calculations_cursor IS
5918: SELECT field8
5919: FROM jl_br_sped_extr_data_t
5920: WHERE request_id = g_concurrent_request_id
5921: AND field1 = 'J100'
5922: AND field4 IS NULL;
5923:
6057:
6058:
6059: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Update');
6060: /*Updating J100 records for account assignments */
6061: UPDATE jl_br_sped_extr_data_t tmp
6062: SET (field2, field3,separator3,
6063: field4 ,
6064: separator4,field5, separator5,field6,separator6,
6065: field7, separator7) = (SELECT --jl_br_sped_extr_data_t_s.nextval,
6061: UPDATE jl_br_sped_extr_data_t tmp
6062: SET (field2, field3,separator3,
6063: field4 ,
6064: separator4,field5, separator5,field6,separator6,
6065: field7, separator7) = (SELECT --jl_br_sped_extr_data_t_s.nextval,
6066: decode(g_agglutination_code_source,'FSG_LINE',to_char(r2.axis_seq),r2.description),
6067: r2.number_characters_indented,
6068: '|',
6069: --get_account_type(get_segment_range_value(g_account_segment,r3.axis_set_id,r3.axis_seq,'LOW')), --field4 --account qualifier for segment_low
6084: AND field1 = 'J100'
6085: AND field8 = J100_rec.field8;
6086: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'after Update');
6087:
6088: UPDATE jl_br_sped_extr_data_t
6089: SET record_seq = jl_br_sped_extr_data_t_s.nextval,
6090: -- field8 = null,
6091: creation_date = g_creation_date,
6092: created_by = g_created_by,
6085: AND field8 = J100_rec.field8;
6086: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'after Update');
6087:
6088: UPDATE jl_br_sped_extr_data_t
6089: SET record_seq = jl_br_sped_extr_data_t_s.nextval,
6090: -- field8 = null,
6091: creation_date = g_creation_date,
6092: created_by = g_created_by,
6093: last_update_date = g_last_update_date,
6127: IF l_cnt >0 THEN
6128: BEGIN
6129: SELECT trim(field4)
6130: INTO l_calc_acct_type
6131: FROM jl_br_sped_extr_data_t
6132: WHERE request_id = g_concurrent_request_id
6133: AND field1 = 'J100'
6134: AND trim(field8) = trim(l_acct_axis_seq);
6135: EXCEPTION
6137: FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while getting the acct type of account''s axis_seq for calculation');
6138: END;
6139:
6140: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_calc_acct_type: '||l_calc_acct_type);
6141: UPDATE jl_br_sped_extr_data_t
6142: SET field4 = l_calc_acct_type
6143: WHERE request_id = g_concurrent_request_id
6144: AND field1 = 'J100'
6145: AND trim(field8) = trim(calc_rec.field8) ;
6151: END IF;
6152: END LOOP;
6153: END LOOP;
6154:
6155: UPDATE jl_br_sped_extr_data_t
6156: SET field8 = null
6157: WHERE request_id = g_concurrent_request_id
6158: AND field1 ='J100';
6159:
6156: SET field8 = null
6157: WHERE request_id = g_concurrent_request_id
6158: AND field1 ='J100';
6159:
6160: UPDATE jl_br_sped_extr_data_t
6161: SET field4 = null
6162: WHERE request_id = g_concurrent_request_id
6163: AND field1 ='J100'
6164: AND field4 = '0'; --field4=0 means all the accounts in the specified account assignments are of not same acct type.
6197: acct_cur ref_cursor;
6198: CURSOR J150_recs_by_jcp IS
6199: SELECT field5, -- amount
6200: field8 -- row_sequence
6201: FROM jl_br_sped_extr_data_t
6202: WHERE request_id = g_concurrent_request_id
6203: AND field1 = 'J150'
6204: ORDER BY record_seq;
6205: BEGIN
6281: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'In acct assignments');
6282: -- FND_FILE.PUT_LINE(FND_FILE.LOG,J150_rec.field8||' '||J150_rec.field5);
6283:
6284:
6285: UPDATE jl_br_sped_extr_data_t tmp
6286: SET (field2, field3,separator3, field4 ,separator4,field5,separator5,
6287: field6, separator6) = (SELECT --jl_br_sped_extr_data_t_s.nextval,
6288: decode(g_agglutination_code_source,'FSG_LINE',to_char(r2.axis_seq),r2.description), --field2
6289: r2.number_characters_indented, --field3
6283:
6284:
6285: UPDATE jl_br_sped_extr_data_t tmp
6286: SET (field2, field3,separator3, field4 ,separator4,field5,separator5,
6287: field6, separator6) = (SELECT --jl_br_sped_extr_data_t_s.nextval,
6288: decode(g_agglutination_code_source,'FSG_LINE',to_char(r2.axis_seq),r2.description), --field2
6289: r2.number_characters_indented, --field3
6290: '|',
6291: r2.description, --field4
6303: AND field8 = J150_rec.field8;
6304: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'After update of account assignements');
6305:
6306: ELSE -- If the row is of type calculations.
6307: UPDATE jl_br_sped_extr_data_t tmp
6308: SET (field2, field3,separator3, field4 ,separator4,field5,separator5,
6309: field6, separator6) = (SELECT --jl_br_sped_extr_data_t_s.nextval,
6310: decode(g_agglutination_code_source,'FSG_LINE',to_char(r2.axis_seq),r2.description), --field2
6311: r2.number_characters_indented, --field3
6305:
6306: ELSE -- If the row is of type calculations.
6307: UPDATE jl_br_sped_extr_data_t tmp
6308: SET (field2, field3,separator3, field4 ,separator4,field5,separator5,
6309: field6, separator6) = (SELECT --jl_br_sped_extr_data_t_s.nextval,
6310: decode(g_agglutination_code_source,'FSG_LINE',to_char(r2.axis_seq),r2.description), --field2
6311: r2.number_characters_indented, --field3
6312: '|',
6313: r2.description, --field4
6325: AND field8 = J150_rec.field8;
6326: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'After update of calculations');
6327: END IF;
6328:
6329: UPDATE jl_br_sped_extr_data_t
6330: SET record_seq = jl_br_sped_extr_data_t_s.nextval,
6331: field8 = null,
6332: creation_date = g_creation_date,
6333: created_by = g_created_by,
6326: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'After update of calculations');
6327: END IF;
6328:
6329: UPDATE jl_br_sped_extr_data_t
6330: SET record_seq = jl_br_sped_extr_data_t_s.nextval,
6331: field8 = null,
6332: creation_date = g_creation_date,
6333: created_by = g_created_by,
6334: last_update_date = g_last_update_date,
6429: DBMS_LOB.READ(l_lob_data,l_amount_to_read,l_read_length,l_data_var_raw); --reading lob data. l_amount_to_read is IN OUT parameter.
6430:
6431: l_data_var_char := UTL_RAW.CAST_TO_VARCHAR2(TO_CHAR(l_data_var_raw));
6432:
6433: INSERT INTO jl_br_sped_extr_data_t
6434: (request_id,
6435: block,
6436: record_seq,
6437: field1,
6446: last_update_date,
6447: last_update_login)
6448: VALUES (g_concurrent_request_id,
6449: 'J',
6450: jl_br_sped_extr_data_t_s.nextval,
6451: decode(l_read_length,1,'J800',null), --filed1
6452: decode(l_read_length,1,'|',null), --seperator1
6453: l_data_var_char, --field2
6454: decode(sign(l_data_length - (l_read_length + l_amount_to_read)),1,null,'|'), --separator2
6531: END IF;
6532: END;
6533:
6534:
6535: INSERT INTO jl_br_sped_extr_data_t
6536: (request_id,
6537: block,
6538: record_seq,
6539: field1,
6558: last_update_date,
6559: last_update_login)
6560: VALUES (g_concurrent_request_id,
6561: 'J', --block
6562: jl_br_sped_extr_data_t_s.nextval, --record_seq
6563: 'J900', --Register (field1)
6564: '|',
6565: 'TERMO DE ENCERRAMENTO', -- field2
6566: '|',
6622: IF ((upper(g_accounting_type) = 'CENTRALIZED' AND g_establishment_id is null) --LE is the company
6623: OR upper(g_accounting_type) = 'DECENTRALIZED')
6624: THEN
6625:
6626: INSERT INTO jl_br_sped_extr_data_t
6627: (request_id,
6628: block,
6629: record_seq,
6630: field1,
6645: last_update_date,
6646: last_update_login)
6647: select g_concurrent_request_id,
6648: 'J',
6649: jl_br_sped_extr_data_t_s.nextval,
6650: 'J930',
6651: '|',
6652: hp.party_name,
6653: '|',
6677: and lk.lookup_code = crole.lookup_code;
6678:
6679: ELSE -- means running in 'CENTRALIZED' mode and establishment acts as company
6680:
6681: INSERT INTO jl_br_sped_extr_data_t
6682: (request_id,
6683: block,
6684: record_seq,
6685: field1,
6700: last_update_date,
6701: last_update_login)
6702: select g_concurrent_request_id,
6703: 'J',
6704: jl_br_sped_extr_data_t_s.nextval,
6705: 'J930',
6706: '|',
6707: hp.party_name,
6708: '|',
6763: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
6764: G_PKG_NAME||': '||l_api_name||'()+');
6765: END IF;
6766:
6767: INSERT INTO jl_br_sped_extr_data_t
6768: (request_id,
6769: block,
6770: record_seq,
6771: field1,
6778: last_update_date,
6779: last_update_login)
6780: VALUES( g_concurrent_request_id,
6781: 'J', --block
6782: jl_br_sped_extr_data_t_s.nextval, --record_seq
6783: 'J990', --Register (field1)
6784: '|',
6785: null, --count(*), --field2
6786: '|',
6826: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
6827: G_PKG_NAME||': '||l_api_name||'()+');
6828: END IF;
6829:
6830: INSERT INTO jl_br_sped_extr_data_t
6831: (request_id,
6832: block,
6833: record_seq,
6834: field1,
6841: last_update_date,
6842: last_update_login)
6843: VALUES( g_concurrent_request_id,
6844: '9', --block
6845: jl_br_sped_extr_data_t_s.nextval, --record_seq
6846: '9001', --Register (field1)
6847: '|',
6848: 0,--null, --decode(count(*),0,1,0), --field2
6849: '|',
6889: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
6890: G_PKG_NAME||': '||l_api_name||'()+');
6891: END IF;
6892:
6893: INSERT INTO jl_br_sped_extr_data_t
6894: (request_id,
6895: block,
6896: record_seq,
6897: field1,
6906: last_update_date,
6907: last_update_login)
6908: SELECT g_concurrent_request_id,
6909: '9', --block
6910: jl_br_sped_extr_data_t_s.nextval, --record_seq
6911: '9900', --Register (field1)
6912: '|',
6913: reg, --field2
6914: '|',
6920: g_last_update_date,
6921: g_last_update_login
6922: FROM (SELECT field1 reg,
6923: COUNT(*) cnt
6924: FROM jl_br_sped_extr_data_t
6925: WHERE request_id = g_concurrent_request_id
6926: AND field1 IS NOT NULL
6927: AND field1 <> '9001'
6928: GROUP BY field1);
6931:
6932: /*9900 register should contain the number of records for each register.
6933: At the time of call to this register_9900, records doesn't exists for 9900, 9990 and 9999 registers.
6934: So need to insert the records 9990 and 9999 registers*/
6935: INSERT INTO jl_br_sped_extr_data_t
6936: (request_id,
6937: block,
6938: record_seq,
6939: field1,
6948: last_update_date,
6949: last_update_login)
6950: VALUES (g_concurrent_request_id,
6951: '9', --block
6952: jl_br_sped_extr_data_t_s.nextval, --record_seq
6953: '9900', --Register (field1)
6954: '|',
6955: '9001', --field2
6956: '|',
6961: g_last_updated_by,
6962: g_last_update_date,
6963: g_last_update_login );
6964:
6965: INSERT INTO jl_br_sped_extr_data_t
6966: (request_id,
6967: block,
6968: record_seq,
6969: field1,
6978: last_update_date,
6979: last_update_login)
6980: VALUES (g_concurrent_request_id,
6981: '9', --block
6982: jl_br_sped_extr_data_t_s.nextval, --record_seq
6983: '9900', --Register (field1)
6984: '|',
6985: '9900', --field2
6986: '|',
6991: g_last_updated_by,
6992: g_last_update_date,
6993: g_last_update_login );
6994:
6995: INSERT INTO jl_br_sped_extr_data_t
6996: (request_id,
6997: block,
6998: record_seq,
6999: field1,
7008: last_update_date,
7009: last_update_login)
7010: VALUES (g_concurrent_request_id,
7011: '9', --block
7012: jl_br_sped_extr_data_t_s.nextval, --record_seq
7013: '9900', --Register (field1)
7014: '|',
7015: '9990', --field2
7016: '|',
7021: g_last_updated_by,
7022: g_last_update_date,
7023: g_last_update_login );
7024:
7025: INSERT INTO jl_br_sped_extr_data_t
7026: (request_id,
7027: block,
7028: record_seq,
7029: field1,
7038: last_update_date,
7039: last_update_login)
7040: VALUES (g_concurrent_request_id,
7041: '9', --block
7042: jl_br_sped_extr_data_t_s.nextval, --record_seq
7043: '9900', --Register (field1)
7044: '|',
7045: '9999', --field2
7046: '|',
7084: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
7085: G_PKG_NAME||': '||l_api_name||'()+');
7086: END IF;
7087:
7088: INSERT INTO jl_br_sped_extr_data_t
7089: (request_id,
7090: block,
7091: record_seq,
7092: field1,
7099: last_update_date,
7100: last_update_login)
7101: VALUES( g_concurrent_request_id,
7102: '9', --block
7103: jl_br_sped_extr_data_t_s.nextval, --record_seq
7104: '9990', --Register (field1)
7105: '|',
7106: null, --count(*), --field2
7107: '|',
7145: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
7146: G_PKG_NAME||': '||l_api_name||'()+');
7147: END IF;
7148:
7149: INSERT INTO jl_br_sped_extr_data_t
7150: (request_id,
7151: block,
7152: record_seq,
7153: field1,
7160: last_update_date,
7161: last_update_login)
7162: VALUES( g_concurrent_request_id,
7163: '9', --block
7164: jl_br_sped_extr_data_t_s.nextval, --record_seq
7165: '9999', --Register (field1)
7166: '|',
7167: null, --count(*), --field2 This field will be updated in update_register_cnt proc at the end of data extraction
7168: '|',
7208: This will be called at the end of data extraction(after calling all registers) as
7209: the insertion of data will be completed at this time.
7210: */
7211:
7212: UPDATE jl_br_sped_extr_data_t
7213: SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
7214: FROM jl_br_sped_extr_data_t
7215: WHERE block='0'
7216: AND request_id = g_concurrent_request_id)
7210: */
7211:
7212: UPDATE jl_br_sped_extr_data_t
7213: SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
7214: FROM jl_br_sped_extr_data_t
7215: WHERE block='0'
7216: AND request_id = g_concurrent_request_id)
7217: WHERE field1 = '0001'
7218: AND request_id = g_concurrent_request_id;
7218: AND request_id = g_concurrent_request_id;
7219:
7220: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 0001 register');
7221:
7222: UPDATE jl_br_sped_extr_data_t
7223: SET field2 = (SELECT COUNT(*)
7224: FROM jl_br_sped_extr_data_t
7225: WHERE block='0'
7226: AND request_id = g_concurrent_request_id)
7220: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 0001 register');
7221:
7222: UPDATE jl_br_sped_extr_data_t
7223: SET field2 = (SELECT COUNT(*)
7224: FROM jl_br_sped_extr_data_t
7225: WHERE block='0'
7226: AND request_id = g_concurrent_request_id)
7227: WHERE field1 = '0990'
7228: AND request_id = g_concurrent_request_id;
7228: AND request_id = g_concurrent_request_id;
7229:
7230: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 0990 register');
7231:
7232: UPDATE jl_br_sped_extr_data_t
7233: SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
7234: FROM jl_br_sped_extr_data_t
7235: WHERE block='I'
7236: AND request_id = g_concurrent_request_id)
7230: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 0990 register');
7231:
7232: UPDATE jl_br_sped_extr_data_t
7233: SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
7234: FROM jl_br_sped_extr_data_t
7235: WHERE block='I'
7236: AND request_id = g_concurrent_request_id)
7237: WHERE field1 = 'I001'
7238: AND request_id = g_concurrent_request_id;
7238: AND request_id = g_concurrent_request_id;
7239:
7240: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated I001 register');
7241:
7242: UPDATE jl_br_sped_extr_data_t
7243: SET field2 = (SELECT COUNT(*)
7244: FROM jl_br_sped_extr_data_t
7245: WHERE block='I'
7246: AND request_id = g_concurrent_request_id)
7240: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated I001 register');
7241:
7242: UPDATE jl_br_sped_extr_data_t
7243: SET field2 = (SELECT COUNT(*)
7244: FROM jl_br_sped_extr_data_t
7245: WHERE block='I'
7246: AND request_id = g_concurrent_request_id)
7247: WHERE field1 = 'I990'
7248: AND request_id = g_concurrent_request_id;
7248: AND request_id = g_concurrent_request_id;
7249:
7250: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated I990 register');
7251:
7252: UPDATE jl_br_sped_extr_data_t
7253: SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
7254: FROM jl_br_sped_extr_data_t
7255: WHERE block='J'
7256: AND request_id = g_concurrent_request_id)
7250: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated I990 register');
7251:
7252: UPDATE jl_br_sped_extr_data_t
7253: SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
7254: FROM jl_br_sped_extr_data_t
7255: WHERE block='J'
7256: AND request_id = g_concurrent_request_id)
7257: WHERE field1 = 'J001'
7258: AND request_id = g_concurrent_request_id;
7258: AND request_id = g_concurrent_request_id;
7259:
7260: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated J001 register');
7261:
7262: UPDATE jl_br_sped_extr_data_t
7263: SET field6 = (SELECT COUNT(*)
7264: FROM jl_br_sped_extr_data_t
7265: WHERE request_id = g_concurrent_request_id
7266: AND field1 IS NOT NULL)
7260: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated J001 register');
7261:
7262: UPDATE jl_br_sped_extr_data_t
7263: SET field6 = (SELECT COUNT(*)
7264: FROM jl_br_sped_extr_data_t
7265: WHERE request_id = g_concurrent_request_id
7266: AND field1 IS NOT NULL)
7267: WHERE field1 = 'J900'
7268: AND request_id = g_concurrent_request_id;
7268: AND request_id = g_concurrent_request_id;
7269:
7270: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated J990 register');
7271:
7272: UPDATE jl_br_sped_extr_data_t
7273: SET field2 = (SELECT COUNT(*)
7274: FROM jl_br_sped_extr_data_t
7275: WHERE block='J'
7276: AND request_id = g_concurrent_request_id
7270: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated J990 register');
7271:
7272: UPDATE jl_br_sped_extr_data_t
7273: SET field2 = (SELECT COUNT(*)
7274: FROM jl_br_sped_extr_data_t
7275: WHERE block='J'
7276: AND request_id = g_concurrent_request_id
7277: AND field1 IS NOT NULL)
7278: WHERE field1 = 'J990'
7279: AND request_id = g_concurrent_request_id;
7280:
7281: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated J990 register');
7282:
7283: UPDATE jl_br_sped_extr_data_t
7284: SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
7285: FROM jl_br_sped_extr_data_t
7286: WHERE block='9'
7287: AND request_id = g_concurrent_request_id)
7281: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated J990 register');
7282:
7283: UPDATE jl_br_sped_extr_data_t
7284: SET field2 = (SELECT DECODE(COUNT(*),0,1,0)
7285: FROM jl_br_sped_extr_data_t
7286: WHERE block='9'
7287: AND request_id = g_concurrent_request_id)
7288: WHERE field1 = '9001'
7289: AND request_id = g_concurrent_request_id;
7289: AND request_id = g_concurrent_request_id;
7290:
7291: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 9001 register');
7292:
7293: UPDATE jl_br_sped_extr_data_t
7294: SET field3 = (SELECT COUNT(*)
7295: FROM jl_br_sped_extr_data_t
7296: WHERE field1='9900'
7297: AND request_id = g_concurrent_request_id)
7291: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 9001 register');
7292:
7293: UPDATE jl_br_sped_extr_data_t
7294: SET field3 = (SELECT COUNT(*)
7295: FROM jl_br_sped_extr_data_t
7296: WHERE field1='9900'
7297: AND request_id = g_concurrent_request_id)
7298: WHERE field1 = '9900'
7299: AND field2 = '9900'
7300: AND request_id = g_concurrent_request_id;
7301:
7302: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 9900 register');
7303:
7304: UPDATE jl_br_sped_extr_data_t
7305: SET field2 = (SELECT COUNT(*)
7306: FROM jl_br_sped_extr_data_t
7307: WHERE block='9'
7308: AND request_id = g_concurrent_request_id)
7302: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 9900 register');
7303:
7304: UPDATE jl_br_sped_extr_data_t
7305: SET field2 = (SELECT COUNT(*)
7306: FROM jl_br_sped_extr_data_t
7307: WHERE block='9'
7308: AND request_id = g_concurrent_request_id)
7309: WHERE field1 = '9990'
7310: AND request_id = g_concurrent_request_id;
7310: AND request_id = g_concurrent_request_id;
7311:
7312: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 9990 register');
7313:
7314: UPDATE jl_br_sped_extr_data_t
7315: SET field5 = (SELECT COUNT(*)
7316: FROM jl_br_sped_extr_data_t
7317: WHERE request_id = g_concurrent_request_id
7318: AND field1 IS NOT NULL)
7312: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated 9990 register');
7313:
7314: UPDATE jl_br_sped_extr_data_t
7315: SET field5 = (SELECT COUNT(*)
7316: FROM jl_br_sped_extr_data_t
7317: WHERE request_id = g_concurrent_request_id
7318: AND field1 IS NOT NULL)
7319: WHERE field1 = 'I030'
7320: AND request_id = g_concurrent_request_id;
7320: AND request_id = g_concurrent_request_id;
7321:
7322: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated I030 register');
7323:
7324: UPDATE jl_br_sped_extr_data_t
7325: SET field2 = (SELECT COUNT(*)
7326: FROM jl_br_sped_extr_data_t
7327: WHERE request_id = g_concurrent_request_id
7328: AND field1 IS NOT NULL)
7322: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated I030 register');
7323:
7324: UPDATE jl_br_sped_extr_data_t
7325: SET field2 = (SELECT COUNT(*)
7326: FROM jl_br_sped_extr_data_t
7327: WHERE request_id = g_concurrent_request_id
7328: AND field1 IS NOT NULL)
7329: WHERE field1 = '9999'
7330: AND request_id = g_concurrent_request_id;
7537: l_api_name CONSTANT VARCHAR2(100) := 'VALIDATE_AFTER_EXTRACT';
7538:
7539: CURSOR valor_deb_cur IS
7540: SELECT DISTINCT I250.field2,I250.field3 -- natural account and costcenter
7541: FROM JL_BR_SPED_EXTR_DATA_T I155,
7542: JL_BR_SPED_EXTR_DATA_T I250
7543: WHERE I155.request_id = g_concurrent_request_id
7544: AND I155.field1 = 'I155'
7545: AND I250.request_id = g_concurrent_request_id
7538:
7539: CURSOR valor_deb_cur IS
7540: SELECT DISTINCT I250.field2,I250.field3 -- natural account and costcenter
7541: FROM JL_BR_SPED_EXTR_DATA_T I155,
7542: JL_BR_SPED_EXTR_DATA_T I250
7543: WHERE I155.request_id = g_concurrent_request_id
7544: AND I155.field1 = 'I155'
7545: AND I250.request_id = g_concurrent_request_id
7546: AND I250.field1 = 'I250'
7552: MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''));
7553:
7554: CURSOR valor_cred_cur IS
7555: SELECT DISTINCT I250.field2,I250.field3 -- natural account and costcenter
7556: FROM JL_BR_SPED_EXTR_DATA_T I155,
7557: JL_BR_SPED_EXTR_DATA_T I250
7558: WHERE I155.request_id = g_concurrent_request_id
7559: AND I155.field1 = 'I155'
7560: AND I250.request_id = g_concurrent_request_id
7553:
7554: CURSOR valor_cred_cur IS
7555: SELECT DISTINCT I250.field2,I250.field3 -- natural account and costcenter
7556: FROM JL_BR_SPED_EXTR_DATA_T I155,
7557: JL_BR_SPED_EXTR_DATA_T I250
7558: WHERE I155.request_id = g_concurrent_request_id
7559: AND I155.field1 = 'I155'
7560: AND I250.request_id = g_concurrent_request_id
7561: AND I250.field1 = 'I250'
7567: MIN(to_number(I155.field7,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''));
7568:
7569: CURSOR vl_lcto_deb_cur IS
7570: SELECT DISTINCT I250.field6 -- Journal Name
7571: FROM JL_BR_SPED_EXTR_DATA_T I200,
7572: JL_BR_SPED_EXTR_DATA_T I250
7573: WHERE I200.request_id = g_concurrent_request_id
7574: AND I200.field1 = 'I200'
7575: AND I250.request_id = g_concurrent_request_id
7568:
7569: CURSOR vl_lcto_deb_cur IS
7570: SELECT DISTINCT I250.field6 -- Journal Name
7571: FROM JL_BR_SPED_EXTR_DATA_T I200,
7572: JL_BR_SPED_EXTR_DATA_T I250
7573: WHERE I200.request_id = g_concurrent_request_id
7574: AND I200.field1 = 'I200'
7575: AND I250.request_id = g_concurrent_request_id
7576: AND I250.field1 = 'I250'
7582:
7583: CURSOR vl_lcto_cred_cur IS
7584: SELECT DISTINCT I250.field6 -- Journal Name
7585: INTO l_cnt
7586: FROM JL_BR_SPED_EXTR_DATA_T I200,
7587: JL_BR_SPED_EXTR_DATA_T I250
7588: WHERE I200.request_id = g_concurrent_request_id
7589: AND I200.field1 = 'I200'
7590: AND I250.request_id = g_concurrent_request_id
7583: CURSOR vl_lcto_cred_cur IS
7584: SELECT DISTINCT I250.field6 -- Journal Name
7585: INTO l_cnt
7586: FROM JL_BR_SPED_EXTR_DATA_T I200,
7587: JL_BR_SPED_EXTR_DATA_T I250
7588: WHERE I200.request_id = g_concurrent_request_id
7589: AND I200.field1 = 'I200'
7590: AND I250.request_id = g_concurrent_request_id
7591: AND I250.field1 = 'I250'
7710: l_cnt :=0;
7711:
7712: SELECT COUNT(*)
7713: INTO l_cnt
7714: FROM jl_br_sped_extr_data_t
7715: WHERE request_id = g_concurrent_request_id
7716: AND field1 ='I155';
7717:
7718: IF l_cnt >0 THEN -- Only if I155 registers exists, then check the validation rules related to that register
7729:
7730: /* SELECT COUNT(*)
7731: INTO l_cnt
7732: FROM (SELECT to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') vl_sld_ini
7733: FROM JL_BR_SPED_EXTR_DATA_T
7734: WHERE request_id = g_concurrent_request_id
7735: AND field1 = 'I155')
7736: WHERE vl_sld_ini<> 0; --VL_SLD_INI */
7737:
7736: WHERE vl_sld_ini<> 0; --VL_SLD_INI */
7737:
7738: SELECT COUNT(*)
7739: INTO l_cnt
7740: FROM JL_BR_SPED_EXTR_DATA_T
7741: WHERE request_id = g_concurrent_request_id
7742: AND field1 = 'I155'
7743: AND field4 <>'0,00'; --vl_sld_ini
7744:
7800:
7801: /* SELECT COUNT(*)
7802: INTO l_cnt
7803: FROM (SELECT to_number(field8,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') vl_sld_fin
7804: FROM JL_BR_SPED_EXTR_DATA_T
7805: WHERE request_id = g_concurrent_request_id
7806: AND field1 = 'I155')
7807: WHERE vl_sld_fin<> 0; */
7808:
7807: WHERE vl_sld_fin<> 0; */
7808:
7809: SELECT COUNT(*)
7810: INTO l_cnt
7811: FROM JL_BR_SPED_EXTR_DATA_T
7812: WHERE request_id = g_concurrent_request_id
7813: AND field1 = 'I155'
7814: AND field8 <>'0,00';
7815:
7870: /* SELECT COUNT(*)
7871: INTO l_cnt
7872: FROM (SELECT to_number(field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') vl_deb,
7873: to_number(field7,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') vl_cred
7874: FROM JL_BR_SPED_EXTR_DATA_T
7875: WHERE request_id = g_concurrent_request_id
7876: AND field1 = 'I155')
7877: WHERE vl_deb <> vl_cred; */
7878:
7878:
7879:
7880: SELECT COUNT(*)
7881: INTO l_cnt
7882: FROM JL_BR_SPED_EXTR_DATA_T
7883: WHERE request_id = g_concurrent_request_id
7884: AND field1 = 'I155'
7885: AND field6 <> field7;
7886:
7946: SELECT
7947: I250.field2,I250.field3,I155.field2,I155.field3
7948: ,SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
7949: , MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
7950: FROM JL_BR_SPED_EXTR_DATA_T I155,
7951: JL_BR_SPED_EXTR_DATA_T I250
7952: WHERE I155.request_id = g_concurrent_request_id
7953: AND I155.field1 = 'I155'
7954: AND I250.request_id = g_concurrent_request_id
7947: I250.field2,I250.field3,I155.field2,I155.field3
7948: ,SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
7949: , MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
7950: FROM JL_BR_SPED_EXTR_DATA_T I155,
7951: JL_BR_SPED_EXTR_DATA_T I250
7952: WHERE I155.request_id = g_concurrent_request_id
7953: AND I155.field1 = 'I155'
7954: AND I250.request_id = g_concurrent_request_id
7955: AND I250.field1 = 'I250'
8023: SELECT
8024: I250.field2,I250.field3,I155.field2,I155.field3
8025: ,SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
8026: , MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
8027: FROM JL_BR_SPED_EXTR_DATA_T I155,
8028: JL_BR_SPED_EXTR_DATA_T I250
8029: WHERE I155.request_id = g_concurrent_request_id
8030: AND I155.field1 = 'I155'
8031: AND I250.request_id = g_concurrent_request_id
8024: I250.field2,I250.field3,I155.field2,I155.field3
8025: ,SUM(to_number(I250.field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
8026: , MIN(to_number(I155.field6,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.'''))
8027: FROM JL_BR_SPED_EXTR_DATA_T I155,
8028: JL_BR_SPED_EXTR_DATA_T I250
8029: WHERE I155.request_id = g_concurrent_request_id
8030: AND I155.field1 = 'I155'
8031: AND I250.request_id = g_concurrent_request_id
8032: AND I250.field1 = 'I250'
8100: l_cnt := 0;
8101:
8102: SELECT count(I250.field6) -- Journal Name
8103: INTO l_cnt
8104: FROM JL_BR_SPED_EXTR_DATA_T I200,
8105: JL_BR_SPED_EXTR_DATA_T I250
8106: WHERE I200.request_id = g_concurrent_request_id
8107: AND I200.field1 = 'I200'
8108: AND I250.request_id = g_concurrent_request_id
8101:
8102: SELECT count(I250.field6) -- Journal Name
8103: INTO l_cnt
8104: FROM JL_BR_SPED_EXTR_DATA_T I200,
8105: JL_BR_SPED_EXTR_DATA_T I250
8106: WHERE I200.request_id = g_concurrent_request_id
8107: AND I200.field1 = 'I200'
8108: AND I250.request_id = g_concurrent_request_id
8109: AND I250.field1 = 'I250'
8172: l_cnt := 0 ;
8173:
8174: SELECT COUNT(I250.field6)-- Journal Name
8175: INTO l_cnt
8176: FROM JL_BR_SPED_EXTR_DATA_T I200,
8177: JL_BR_SPED_EXTR_DATA_T I250
8178: WHERE I200.request_id = g_concurrent_request_id
8179: AND I200.field1 = 'I200'
8180: AND I250.request_id = g_concurrent_request_id
8173:
8174: SELECT COUNT(I250.field6)-- Journal Name
8175: INTO l_cnt
8176: FROM JL_BR_SPED_EXTR_DATA_T I200,
8177: JL_BR_SPED_EXTR_DATA_T I250
8178: WHERE I200.request_id = g_concurrent_request_id
8179: AND I200.field1 = 'I200'
8180: AND I250.request_id = g_concurrent_request_id
8181: AND I250.field1 = 'I250'
8250: l_cnt := 0;
8251:
8252: SELECT COUNT(*)
8253: INTO l_cnt
8254: FROM JL_BR_SPED_EXTR_DATA_T
8255: WHERE request_id = g_concurrent_request_id
8256: AND field1 = 'I310';
8257:
8258: IF l_cnt >0 THEN
8260: /* SELECT COUNT(*)
8261: INTO l_cnt
8262: FROM (SELECT to_number(field4,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') deb,
8263: to_number(field5,'99999999999999990D00','NLS_NUMERIC_CHARACTERS = '',.''') cred
8264: FROM JL_BR_SPED_EXTR_DATA_T
8265: WHERE request_id = g_concurrent_request_id
8266: AND field1 = 'I310')
8267: WHERE deb <> cred; */
8268:
8267: WHERE deb <> cred; */
8268:
8269: SELECT COUNT(*)
8270: INTO l_cnt
8271: FROM JL_BR_SPED_EXTR_DATA_T
8272: WHERE request_id = g_concurrent_request_id
8273: AND field1 = 'I310'
8274: AND field4 <> field5;
8275:
8329: l_cnt := 0;
8330:
8331: SELECT COUNT(*)
8332: INTO l_cnt
8333: FROM jl_br_sped_extr_data_t
8334: WHERE field1 = 'J930'
8335: AND field5 = '900';
8336:
8337: IF l_cnt < 1 THEN
8371: l_cnt := 0;
8372:
8373: SELECT COUNT(*)
8374: INTO l_cnt
8375: FROM jl_br_sped_extr_data_t
8376: WHERE field1 = 'J930'
8377: AND field5 <> '900';
8378:
8379: IF l_cnt < 1 THEN
8479:
8480: -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Purging data of request_id:' || l_request_id);
8481:
8482: DELETE --purging the data as user running the extract prg for the combination which is already existing in temp table
8483: FROM jl_br_sped_extr_data_t
8484: WHERE request_id = l_request_id;
8485:
8486: UPDATE jl_br_sped_extr_param
8487: SET data_exist = 'N'
8559: WHERE request_id = g_concurrent_request_id ;*/
8560:
8561: CURSOR accts_with_invalid_jrl_amounts IS
8562: SELECT field2 natural_acct
8563: FROM jl_br_sped_extr_data_t
8564: WHERE request_id = g_concurrent_request_id
8565: AND field4 like '%####%'
8566: AND field1='I250';
8567:
8742: --- custom code hook call for register I015
8743:
8744: IF substrb(g_bookkeeping_type,1,1) <> 'G' THEN
8745:
8746: SELECT jl_br_sped_extr_data_t_s.CURRVAL INTO l_sequence_value
8747: FROM DUAL;
8748:
8749: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling code hook, which populates I015 for Integrated Receiving system');
8750: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Code Hook Starting Sequence Value :'||l_sequence_value);
8770: ,g_cost_center_segment --can be null if the customer doesn't have the cost center setup.
8771: ,g_bookkeeping_type
8772: ,g_concurrent_request_id);
8773:
8774: SELECT jl_br_sped_extr_data_t_s.CURRVAL INTO l_sequence_value
8775: FROM DUAL;
8776:
8777: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Code Hook Ending Sequence Value :'||l_sequence_value);
8778: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
8852: -- custom code hook call for register I200 and I250
8853:
8854: IF substrb(g_bookkeeping_type,1,1) <> 'B' THEN
8855:
8856: SELECT jl_br_sped_extr_data_t_s.CURRVAL INTO l_sequence_value
8857: FROM DUAL;
8858:
8859: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling code hook, which populates I200 and I250 for Integrated Receiving system');
8860: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Code Hook Starting Sequence Value :'||l_sequence_value);
8880: ,g_cost_center_segment --can be null if the customer doesn't have the cost center setup.
8881: ,g_bookkeeping_type
8882: ,g_concurrent_request_id);
8883:
8884: SELECT jl_br_sped_extr_data_t_s.CURRVAL INTO l_sequence_value
8885: FROM DUAL;
8886:
8887: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Code Hook Ending Sequence Value :'||l_sequence_value);
8888: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
8989:
8990: BEGIN
8991: SELECT count(*)
8992: INTO l_cnt
8993: FROM jl_br_sped_extr_data_t
8994: WHERE request_id = g_concurrent_request_id
8995: AND field1 = 'J100';
8996:
8997: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'cnt of rows from FSG for bal: '||l_cnt);
9000: WHEN NO_DATA_FOUND THEN
9001: FND_FILE.PUT_LINE(FND_FILE.LOG,'No records found');
9002: END;
9003:
9004: DELETE FROM jl_br_sped_extr_data_t
9005: WHERE field1 ='J100'
9006: AND request_id = g_concurrent_request_id
9007: AND field6 is null;
9008:
9005: WHERE field1 ='J100'
9006: AND request_id = g_concurrent_request_id
9007: AND field6 is null;
9008:
9009: DELETE FROM jl_br_sped_extr_data_t
9010: WHERE field1 ='J150'
9011: AND request_id = g_concurrent_request_id
9012: AND field5 is null;
9013:
9013:
9014: BEGIN
9015: SELECT count(*)
9016: INTO l_cnt
9017: FROM jl_br_sped_extr_data_t
9018: WHERE request_id = g_concurrent_request_id
9019: AND field1 = 'J100';
9020:
9021: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'cnt of rows from FSG for bal: '||l_cnt);
9148: printable_chars := printable_chars||fnd_global.local_chr(125)||fnd_global.local_chr(126);
9149:
9150: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Removing non printable characters');
9151:
9152: UPDATE jl_br_sped_extr_data_t
9153: SET field2 = translate(field2, printable_chars|| non_printable_chars, printable_chars),
9154: field3 = translate(field3, printable_chars|| non_printable_chars, printable_chars),
9155: field4 = translate(field4, printable_chars|| non_printable_chars, printable_chars),
9156: field5 = translate(field5, printable_chars|| non_printable_chars, printable_chars),