[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Print ( 'Val and Insert >>>> '|| 'Setting the status '|| x_orig_status);
FUNCTION insert_rejections(
p_batch_id IN NUMBER,
p_trx_id IN NUMBER default null,
p_dist_id IN NUMBER default null,
p_batch_dist_id IN NUMBER default null,
p_reject_code IN VARCHAR2 default null,
p_reject_reason IN VARCHAR2
) RETURN BOOLEAN IS
debug_info VARCHAR2(500);
debug_info := '(Insert Rejections 1) Insert into FUN_INTERFACE_REJECTIONS, REJECT REASON: '||p_reject_reason;
Print('Insert Rejections >>'||debug_info);
Print('Insert Rejections >>'||'Btc id:' || to_char(p_batch_id));
Print('Insert Rejections >>'||'gl_date:' || to_char(p_reject_code));
INSERT INTO FUN_INTERFACE_REJECTIONS (
batch_id,
trx_id,
dist_id,
batch_dist_id,
reject_code,
reject_reason
)
VALUES(
p_batch_id,
nvl(p_trx_id,null),
nvl(p_dist_id,null),
nvl(p_batch_dist_id,null),
p_reject_code,
p_reject_reason
);
Print('Insert Rejections >>'||'Sucessfully inserted into Rejections');
Print('Insert Rejections >>'||'Insert into Rejections Table Failed');
END insert_rejections;
p_insert IN VARCHAR2 ,
p_batch_rec IN OUT NOCOPY FULL_BATCH_REC_TYPE,
p_trx_tbl IN OUT NOCOPY FULL_TRX_TBL_TYPE,
p_init_dist_tbl IN OUT NOCOPY FULL_INIT_DIST_TBL_TYPE,
p_dist_lines_tbl IN OUT NOCOPY FULL_DIST_LINE_TBL_TYPE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_BATCH';
Select legal_entity_id
From Xle_Firstparty_Information_V
Where party_id = cp_le_party_id;
Print('Val and Insert >>>>'||'Start of the API');
SAVEPOINT Fun_Trx_Val_And_Insert_PUB;
Print('Val and Insert >>>>'||'API Compatibilty Check');
Print('Val and Insert >>>>'||'Non compatible API call');
Print('Val and Insert >>>>'||'Populating Parameters to be sent to Batch Validation API');
Print('Val and Insert >>>>'||'Value of message list '||l_init_msg_list);
Print('Val and Insert >>>>'||'Population of Trx_Tbl');
Print('Val and Insert >>>>'||'Populate Batch Dist');
Print('Val and Insert >>>>'||'BATCH DIST TBL NOT NULL '||P_INIT_dist_tbl.COUNT);
Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).batch_dist_id);
Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).line_number);
Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).batch_id);
Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).ccid);
Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).amount_cr);
Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).amount_Dr);
Print('Val and Insert >>>>'||'Dist Lines Tbl');
Print('Val and Insert >>>>'||'Dist lines not null');
Print('Val and Insert >>>>'||'Record Details for '||l_line_count);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).trx_id);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).dist_id);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).line_id);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).party_id);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).party_type_flag);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).dist_type_flag);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).batch_dist_id);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).amount_cr);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).amount_dr);
Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).ccid); --Bug 3603338
Print('Val and Insert >>>>'|| 'End of Record Details');
Print('Val and Insert >>>>'||'Validating the Batch');
Print('Val and Insert >>>>'||'Validating Batch Id');
SELECT batch_id INTO l_unique_batch_id
FROM fun_trx_batches
WHERE batch_id=l_batch_rec.batch_id;
IF (insert_rejections(p_batch_id => l_batch_rec.batch_id,
p_reject_reason => l_msg) <> TRUE) THEN
Print('Val and Insert >>>>'||'insert_rejections of invalid batches failure');
Print('Val and Insert >>>>'||'Batch Id is unique');
p_insert => nvl(p_insert,FND_API.G_TRUE),
p_batch_rec => l_batch_rec,
p_trx_tbl => l_trx_tbl,
p_init_dist_tbl => l_init_dist_tbl,
p_dist_lines_tbl => l_dist_lines_tbl
);
Print('Val and Insert >>>>'||'Batch Validation Complete'|| l_return_status|| 'message'||l_msg);
/* Insert into Rejections Table with all the reason of error */
If (l_return_status = FND_API.G_RET_STS_ERROR) then
If (p_calling_sequence = 'Intercompany Import Program') then
IF l_msg_count >= 1 THEN
FOR i IN 1..l_msg_count
LOOP
l_msg := FND_MSG_PUB.Get( p_msg_index => l_msg_count,
p_encoded => FND_API.G_FALSE);
If (insert_rejections(
p_batch_id => l_batch_rec.batch_id,
p_reject_reason => l_msg
) <> TRUE)
then
Print('Val and Insert >>>>'||'insert_rejections of invalid batches failure');
End if; -- Insert Rejections
Print ('Val and Insert >>>> '|| 'Unexpected error after batch val');
l_trx_tbl.delete;
l_init_dist_tbl.delete;
l_dist_lines_tbl.delete;
Print('Val and Insert >>>>'||'Populating Parameters to be sent to Trx Header Validation API');
Print('Val and Insert >>>>'||p_trx_tbl.count);
Print('Val and Insert >>>>'||'Populating trx_rec_type');
Print('Val and Insert >>>>'||'Populating Dist_Lines Tbl');
Print('Val and Insert >>>>'||'Validating the Trx Header');
Print('Val and Insert >>>>'||'Transaction Validation Complete');
Print('Val and Insert >>>>'||'Return Staus from Txn validate '||l_return_status);
Print('Val and Insert >>>>'||'Inserting Trx Header Reject Reasons');
UPDATE fun_interface_headers set import_status_code = 'R' Where trx_id = p_trx_tbl(l_head_count).trx_id;
If (insert_rejections(
p_batch_id => p_trx_tbl(l_head_count).batch_id,
p_trx_id => p_trx_tbl(l_head_count).trx_id,
p_reject_reason => l_msg
)<> TRUE)
then
Print('Val and Insert >>>>'||'insert_rejections of invalid transactions failure');
End if; -- Insert Rejections
UPDATE fun_interface_headers set import_status_code = 'A'
Where trx_id = p_trx_tbl(l_head_count).trx_id;
l_dist_lines_tbl.delete;
Print('Val and Insert >>>>'||'Populating Parameters to be sent to Batch Dist Validation API');
Print('Val and Insert >>>>'||'Validating the Batch Distributions');
Print('Val and Insert >>>> '||' Status after Init_dist_validate 2'|| l_return_status);
Print('Val and Insert >>>> '||' Status after Init_dist_validate 3'|| l_return_status);
Print('Inserting Batch Distributions Reject Reasons');
UPDATE fun_interface_batchdists set import_status_code = 'R'
Where batch_dist_id = p_init_dist_tbl(l_line_count).batch_dist_id;
If (insert_rejections(
p_batch_id => p_init_dist_tbl(l_line_count).batch_id,
p_batch_dist_id => p_init_dist_tbl(l_line_count).batch_dist_id,
p_reject_reason => l_msg
)<> TRUE) Then
Print('insert_rejections of invalid batch dist records failure');
UPDATE fun_interface_batchdists set import_status_code = 'A'
Where batch_dist_id = p_init_dist_tbl(l_line_count).batch_dist_id;
Print('Val and Insert >>>>'||'Populating Parameters to be sent to Dist Lines API');
select trxh.to_ledger_id
into l_to_ledger_id
from fun_interface_headers trxH,
fun_interface_dist_lines dist
where dist.trx_id = trxH.trx_id
and dist.dist_id = l_dist_lines_rec_type.dist_id;
Print('Val And Insert Debug >>> ' || l_init_msg_list );
Print('Val And Insert Debug >>> ' || nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL));
Print('Val And Insert Debug >>> ' || l_to_le_id);
Print('Val And Insert Debug >>> ' || l_to_ledger_id);
Print('Val And Insert Debug >>> ' || p_batch_rec.from_ledger_id);
Print('Val And Insert Debug >>> ' || l_return_status);
Print('Val And Insert Debug >>> ' || l_msg_count);
Print('Val And Insert Debug >>> ' || l_msg_data);
Print('Val And Insert Debug >>> ' || p_batch_rec.from_le_id);
Print('Val and Insert >>>>'||'Validating the Dist Lines');
Print('Val and Insert >>>> '||' Status after Init_dist_validate '|| l_return_status);
Print('Val and Insert >>>>'||'Inserting Dist Lines Reject Reasons');
UPDATE fun_interface_dist_lines set import_status_code = 'R'
Where dist_id = p_dist_lines_tbl(l_line_count).dist_id;
If (insert_rejections(
p_batch_id =>l_batch_rec.batch_id,
p_dist_id => p_dist_lines_tbl(l_line_count).dist_id,
p_reject_reason => l_msg
)<> TRUE) Then
Print('Val and Insert >>>>'||'insert_rejections of invalid dist lines failure');
UPDATE fun_interface_dist_lines set import_status_code = 'A'
Where dist_id = p_dist_lines_tbl(l_line_count).dist_id;
Select fun_trx_batches_s.nextval INTO l_batch_id from dual;
/* If all Validations pass then the record should be inserted into Fun Tables with status as New */
If x_return_Status = FND_API.G_RET_STS_SUCCESS then
If (nvl(p_insert, FND_API.G_TRUE) = FND_API.G_TRUE) THEN
Print ('Populating User Id');
Print('Val and Insert >>>>'||'Inserting into fun_trx_batches Table');
INSERT into fun_trx_batches(
batch_id,
batch_number,
initiator_id,
from_le_id,
from_ledger_id,
control_total,
running_total_cr,
running_total_dr,
currency_code,
exchange_rate_type,
status,
description,
note,
trx_type_id,
trx_type_code,
gl_date,
batch_date,
reject_allow_flag, -- changed
original_batch_id,
reversed_batch_id,
from_recurring_batch_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
auto_proration_flag )
VALUES(
l_batch_id,
p_batch_rec.batch_number,
p_batch_rec.initiator_id,
p_batch_rec.from_le_id,
p_batch_rec.from_ledger_id,
p_batch_rec.control_total,
p_batch_rec.running_total_cr,
p_batch_rec.running_total_dr,
p_batch_rec.currency_code,
p_batch_rec. exchange_rate_type,
p_batch_rec.status,
p_batch_rec.description,
p_batch_rec.note,
p_batch_rec.trx_type_id,
p_batch_rec. trx_type_code,
p_batch_rec.gl_date,
p_batch_rec.batch_date,
p_batch_rec.reject_allow_flag,
p_batch_rec.original_batch_id,
p_batch_rec.reversed_batch_id,
p_batch_rec.from_recurring_batch_id,
p_batch_rec.attribute1,
p_batch_rec.attribute2,
p_batch_rec.attribute3,
p_batch_rec.attribute4,
p_batch_rec.attribute5,
p_batch_rec.attribute6,
p_batch_rec.attribute7,
p_batch_rec.attribute8,
p_batch_rec.attribute9,
p_batch_rec.attribute10,
p_batch_rec.attribute11,
p_batch_rec.attribute12,
p_batch_rec.attribute13,
p_batch_rec.attribute14,
p_batch_rec.attribute15,
p_batch_rec.attribute_category,
l_user,
sysdate,
l_user,
sysdate,
l_login,
'N'
);
Print('Val and Insert >>>>'||'Inserting into fun_trx_headers Table');
Print('Val and Insert >>>>>' ||'Generating Trx Number');
INSERT into fun_trx_headers (
trx_id,
trx_number,
initiator_id,
recipient_id,
to_le_id,
to_ledger_id,
batch_id,
status,
init_amount_cr,
init_amount_dr,
reci_amount_cr,
reci_amount_dr,
ar_invoice_number,
invoice_flag,
approver_id,
approval_date,
original_trx_id,
reversed_trx_id,
from_recurring_trx_id,
initiator_instance_flag,
recipient_instance_flag,
description,
reject_reason,
init_wf_key,
reci_wf_key,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES(
Fun_trx_headers_s.nextval,
p_trx_tbl(l_head_count).trx_number,
p_batch_rec.initiator_id,
p_trx_tbl(l_head_count).recipient_id,
p_trx_tbl(l_head_count).to_le_id,
p_trx_tbl(l_head_count).to_ledger_id,
l_batch_id,
p_trx_tbl(l_head_count).status,
p_trx_tbl(l_head_count).init_amount_cr,
p_trx_tbl(l_head_count).init_amount_dr,
p_trx_tbl(l_head_count).reci_amount_cr,
p_trx_tbl(l_head_count).reci_amount_dr,
p_trx_tbl(l_head_count).ar_invoice_number,
p_trx_tbl(l_head_count).invoice_flag,
p_trx_tbl(l_head_count).approver_id,
p_trx_tbl(l_head_count).approval_date,
p_trx_tbl(l_head_count).original_trx_id,
p_trx_tbl(l_head_count).reversed_trx_id,
p_trx_tbl(l_head_count).from_recurring_trx_id,
p_trx_tbl(l_head_count).initiator_instance_flag,
p_trx_tbl(l_head_count).recipient_instance_flag,
p_trx_tbl(l_head_count).description,
p_trx_tbl(l_head_count).reject_reason,
p_trx_tbl(l_head_count).init_wf_key,
p_trx_tbl(l_head_count).reci_wf_key,
p_trx_tbl(l_head_count).attribute1,
p_trx_tbl(l_head_count).attribute2,
p_trx_tbl(l_head_count).attribute3,
p_trx_tbl(l_head_count).attribute4,
p_trx_tbl(l_head_count).attribute5,
p_trx_tbl(l_head_count).attribute6,
p_trx_tbl(l_head_count).attribute7,
p_trx_tbl(l_head_count).attribute8,
p_trx_tbl(l_head_count).attribute9,
p_trx_tbl(l_head_count).attribute10,
p_trx_tbl(l_head_count).attribute11,
p_trx_tbl(l_head_count).attribute12,
p_trx_tbl(l_head_count).attribute13,
p_trx_tbl(l_head_count).attribute14,
p_trx_tbl(l_head_count).attribute15,
p_trx_tbl(l_head_count).attribute_category,
l_user,
sysdate,
l_user,
sysdate,
l_login
);
Print('Val and Insert >>>>'||'Inserting into fun_trx_lines Table');
Print('Val and Insert >>>>>' ||'Generating Trx Lines');
Print('Val and Insert >>>>>' ||'Generating trx line number errored out ');
INSERT into fun_trx_lines (
line_id,
trx_id,
line_number,
line_type_flag,
init_amount_cr,
init_amount_dr,
reci_amount_cr,
reci_amount_dr,
description,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES(
Fun_trx_lines_s.nextval,
Fun_trx_headers_s.currval,
--l_sequence_number, commented out untill generation of doc seq
--1234, Taking line_id as line_number instead of hardcoding it. Bug 3603338
Fun_trx_lines_s.currval,
'I',
p_trx_tbl(l_head_count).init_amount_cr,
p_trx_tbl(l_head_count).init_amount_dr,
p_trx_tbl(l_head_count).reci_amount_cr,
p_trx_tbl(l_head_count).reci_amount_dr,
p_trx_tbl(l_head_count).description,
l_user,
sysdate,
l_user,
sysdate,
l_login
);
Print('Val and Insert >>>>'||'Inserting into fun_trx_dist_lines Table');
Print('Val and Insert >>>>>' ||'Generating Trx dist Lines');
Print('Val and Insert >>>>>' ||'Generating trx dist line number errored out ');
INSERT into fun_dist_lines
(
dist_id,
line_id,
dist_number,
party_id,
party_type_flag,
dist_type_flag,
batch_dist_id,
amount_cr,
amount_dr,
ccid,
description,
auto_generate_flag,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
trx_id
)
VALUES(
Fun_dist_lines_s.nextval,
Fun_trx_lines_s.CURRVAL,
Fun_dist_lines_s.currval, --Bug 3603338
p_dist_lines_tbl(l_line_count).party_id,
p_dist_lines_tbl(l_line_count).party_type_flag,
p_dist_lines_tbl(l_line_count).dist_type_flag,
p_dist_lines_tbl(l_line_count).batch_dist_id,
p_dist_lines_tbl(l_line_count).amount_cr,
p_dist_lines_tbl(l_line_count).amount_dr,
p_dist_lines_tbl(l_line_count).ccid,
p_dist_lines_tbl(l_line_count).description,
--p_dist_lines_tbl(l_line_count).auto_generate_flag, is not avl anywhere for now hardcoding to N Bug 3603338
'N',
p_dist_lines_tbl(l_line_count).attribute1,
p_dist_lines_tbl(l_line_count).attribute2,
p_dist_lines_tbl(l_line_count).attribute3,
p_dist_lines_tbl(l_line_count).attribute4,
p_dist_lines_tbl(l_line_count).attribute5,
p_dist_lines_tbl(l_line_count).attribute6,
p_dist_lines_tbl(l_line_count).attribute7,
p_dist_lines_tbl(l_line_count).attribute8,
p_dist_lines_tbl(l_line_count).attribute9,
p_dist_lines_tbl(l_line_count).attribute10,
p_dist_lines_tbl(l_line_count).attribute11,
p_dist_lines_tbl(l_line_count).attribute12,
p_dist_lines_tbl(l_line_count).attribute13,
p_dist_lines_tbl(l_line_count).attribute14,
p_dist_lines_tbl(l_line_count).attribute15,
p_dist_lines_tbl(l_line_count).attribute_category,
l_user,
sysdate,
l_user,
sysdate,
l_login,
Fun_trx_headers_s.currval
);
Print('Val and Insert >>>>'||'Raise Business Event');
select FUN_TRX_BATCHES_S.CURRVAL into l_batch_id from dual;
select FUN_TRX_HEADERS_S.CURRVAL into l_trx_id from dual;
Print('Val and Insert >>>>'||'Inserting into fun_trx_batch_dist Table');
INSERT into fun_batch_dists (
batch_dist_id,
line_number,
batch_id,
ccid,
amount_cr,
amount_dr,
description,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
fun_batch_dist_s.nextval,
p_init_dist_tbl(l_line_count).line_number,
l_batch_id,
p_init_dist_tbl(l_line_count).ccid,
p_init_dist_tbl(l_line_count).amount_cr,
p_init_dist_tbl(l_line_count).amount_dr,
p_init_dist_tbl(l_line_count).description,
l_user,
sysdate,
l_user,
sysdate,
l_login
);
Print('Val and Insert >>>>'||'Raise Business Event');
UPDATE fun_trx_batches
SET status = 'SENT'
WHERE batch_id = l_batch_id;
UPDATE fun_trx_headers
SET status = 'SENT'
WHERE batch_id = l_batch_id;
End if; -- p_insert true
Print('Val and Insert >>>>'||'End of the API');
ROLLBACK TO Fun_Trx_Val_And_Insert_PUB;
Print('Val and Insert >>>>'||'Unexpected error occurred -'||SQLERRM);
Print('Val and Insert >>>>'||'Details of Error');
ROLLBACK TO Fun_Trx_Val_And_Insert_PUB;
Print('Val and Insert >>>>'||'When Other');
ROLLBACK TO Fun_Trx_Val_And_Insert_PUB;
Print('Val and Insert >>>>'||'Exception others- '||SQLERRM);