/* ===============================================
* PROGRAM NAME: process_request
*
* DESCRIPTION:
* 主程序
* HISTORY:
* 1.00 2020-07-08 wang.chen Creation
*
* ==============================================*/
PROCEDURE Process_Request(p_Init_Msg_List IN VARCHAR2
,p_Commit IN VARCHAR2
,x_Return_Status OUT VARCHAR2
,x_Msg_Count OUT NUMBER
,x_Msg_Data OUT VARCHAR2
,p_Set_Of_Bks_Id IN NUMBER
,p_Org_Id IN NUMBER
,p_Customer_Id IN NUMBER
,p_Trx_Batch_Id IN NUMBER
,p_Customer_Trx_Number IN VARCHAR2
,p_App_Date IN VARCHAR2
,p_App_Gl_Date IN VARCHAR2
,p_Trx_Type IN VARCHAR2
,p_Project_Flag IN VARCHAR2
,p_Project IN VARCHAR2
,p_Order_By_Type IN VARCHAR2) IS
CURSOR Cur_Cm IS
SELECT Rct.Customer_Trx_Id
,Rct.Org_Id
,Rct.Trx_Number
,Rct.Trx_Date
,Ctt.Name Trx_Type_Name
,Ctt.Type
,Rac_Bill_Party.Party_Name
,Rac_Bill.Account_Number
,Rct.Attribute1 Project_Number
,Aps.Acctd_Amount_Due_Remaining Amount
,Aps.Payment_Schedule_Id
FROM Ra_Customer_Trx_All Rct
,Ra_Customer_Trx_Lines_All Rctl
,Ra_Batches_All Rb
,Ra_Cust_Trx_Types_All Ctt
,Hz_Cust_Accounts Rac_Bill
,Hz_Parties Rac_Bill_Party
,Ar_Payment_Schedules Aps
WHERE 1 = 1
AND Rct.Customer_Trx_Id = Rctl.Customer_Trx_Id
AND Rctl.Line_Type IN ('LINE'
,'CB'
,'CHARGES')
AND Rct.Batch_Id = Rb.Batch_Id
AND Rct.Org_Id = Ctt.Org_Id
AND Rct.Cust_Trx_Type_Id = Ctt.Cust_Trx_Type_Id
AND Ctt.Type <> 'BR'
AND Ctt.Type = 'CM' --贷项通知单
AND Rct.Bill_To_Customer_Id = Rac_Bill.Cust_Account_Id
AND Rac_Bill.Party_Id = Rac_Bill_Party.Party_Id
AND Rct.Customer_Trx_Id = Aps.Customer_Trx_Id
AND Aps.Acctd_Amount_Due_Remaining < 0 --判断是否未结
--PARAMETER
AND Rct.Org_Id = p_Org_Id
AND (Rac_Bill.Cust_Account_Id = p_Customer_Id OR
p_Customer_Id IS NULL)
AND (Rb.Batch_Id = p_Trx_Batch_Id OR p_Trx_Batch_Id IS NULL)
AND (Rct.Trx_Number = p_Customer_Trx_Number OR
p_Customer_Trx_Number IS NULL)
AND (Ctt.Name = p_Trx_Type OR p_Trx_Type IS NULL)
AND (Rct.Attribute1 = p_Project OR p_Project IS NULL)
-- AND Rct.Trx_Number IN ('JNG00200305RQ23533')
ORDER BY Rct.Trx_Date
,Aps.Acctd_Amount_Due_Remaining;
l_Process_Name CONSTANT VARCHAR2(30) := 'process_request';
l_Return_Status VARCHAR2(1);
l_Msg_Count NUMBER;
l_Msg_Data VARCHAR2(1000);
l_Message_List VARCHAR2(1000);
l_Msg_Index_Out NUMBER;
l_Cm_App_Rec Ar_Cm_Api_Pub.Cm_App_Rec_Type;
l_Out_Rec_Application_Id NUMBER;
l_Acctd_Amount_Applied_From NUMBER;
l_Acctd_Amount_Applied_To NUMBER;
l_Cm_Amount NUMBER;
l_Inv_Amount NUMBER;
l_Cm_Flag VARCHAR2(10);
l_Inv_Flag VARCHAR2(10);
l_Amount NUMBER;
l_User_Name VARCHAR2(240);
l_Fail_Count NUMBER;
l_Status VARCHAR2(240);
BEGIN
---初始化
Fnd_Global.Apps_Initialize(Fnd_Global.User_Id
,Fnd_Global.Resp_Id
,Fnd_Global.Resp_Appl_Id);
Mo_Global.Init('AR');
Mo_Global.Set_Policy_Context(p_Access_Mode => 'S'
,p_Org_Id => p_Org_Id);
l_Fail_Count := 0;
FOR Rec_Cm IN Cur_Cm LOOP
Log('开始');
--校验规则
l_Cm_Amount := 0;
l_Inv_Amount := 0;
l_Cm_Amount := Abs(Rec_Cm.Amount);
--获取标准AR发票的总金额
SELECT SUM(Aps.Acctd_Amount_Due_Remaining)
INTO l_Inv_Amount
FROM Ra_Customer_Trx_All Rct
,Ra_Customer_Trx_Lines_All Rctl
,Ra_Cust_Trx_Types_All Ctt
,Ra_Cust_Trx_Types_All Ctt1
,Hz_Cust_Accounts Rac_Bill
,Hz_Parties Rac_Bill_Party
,Ar_Payment_Schedules Aps
WHERE 1 = 1
AND Rct.Customer_Trx_Id = Rctl.Customer_Trx_Id
AND Rctl.Line_Type IN ('LINE'
,'CB'
,'CHARGES')
AND Rct.Org_Id = Ctt.Org_Id
AND Rct.Cust_Trx_Type_Id = Ctt.Cust_Trx_Type_Id
AND Ctt.Type <> 'BR'
AND Ctt.Type = 'INV' --事务处理发票
AND Ctt.Credit_Memo_Type_Id = Ctt1.Cust_Trx_Type_Id
AND Rct.Bill_To_Customer_Id = Rac_Bill.Cust_Account_Id
AND Rac_Bill.Party_Id = Rac_Bill_Party.Party_Id
AND Rct.Customer_Trx_Id = Aps.Customer_Trx_Id
AND Aps.Acctd_Amount_Due_Remaining > 0
AND Rct.Org_Id = p_Org_Id
AND Rac_Bill_Party.Party_Name = Rec_Cm.Party_Name
AND Ctt1.Name = Rec_Cm.Trx_Type_Name
AND ((Nvl(Rct.Attribute1
,'-99999') = Nvl(Rec_Cm.Project_Number
,'-99999') AND p_Project_Flag = 'Y') OR
Nvl(p_Project_Flag
,'N') = 'N');
Log('l_Cm_Amount:' || l_Cm_Amount);
Log('l_Inv_Amount:' || l_Inv_Amount);
IF (l_Cm_Amount >= l_Inv_Amount) THEN
---标准AR发票核销贷项通知单
l_Cm_Flag := 'N';
-- l_Inv_Flag := 'Y';
ELSE
--贷项通知单核销标准AR发票
l_Cm_Flag := 'Y';
-- l_Inv_Flag := 'N';
END IF;
--按客户，项目，事务处理类型匹配
FOR Rec_Inv IN (SELECT Rct.Customer_Trx_Id
,Rct.Org_Id
,Rct.Trx_Number
,Rct.Trx_Date
,Ctt.Name Trx_Type_Name
,Ctt.Type
,Rac_Bill_Party.Party_Name
,Rac_Bill.Account_Number
,Rct.Attribute1 Project_Number
,Aps.Acctd_Amount_Due_Remaining Amount
,Aps.Cash_Receipt_Id
,Aps.Payment_Schedule_Id
FROM Ra_Customer_Trx_All Rct
,Ra_Customer_Trx_Lines_All Rctl
,Ra_Cust_Trx_Types_All Ctt
,Ra_Cust_Trx_Types_All Ctt1
,Hz_Cust_Accounts Rac_Bill
,Hz_Parties Rac_Bill_Party
,Ar_Payment_Schedules Aps
WHERE 1 = 1
AND Rct.Customer_Trx_Id = Rctl.Customer_Trx_Id
AND Rctl.Line_Type IN ('LINE'
,'CB'
,'CHARGES')
AND Rct.Org_Id = Ctt.Org_Id
AND Rct.Cust_Trx_Type_Id = Ctt.Cust_Trx_Type_Id
AND Ctt.Type <> 'BR'
AND Ctt.Type = 'INV' --事务处理发票
AND Ctt.Credit_Memo_Type_Id = Ctt1.Cust_Trx_Type_Id
AND Rct.Bill_To_Customer_Id =
Rac_Bill.Cust_Account_Id
AND Rac_Bill.Party_Id = Rac_Bill_Party.Party_Id
AND Rct.Customer_Trx_Id = Aps.Customer_Trx_Id
AND Aps.Acctd_Amount_Due_Remaining > 0
AND Rct.Org_Id = p_Org_Id
AND Rac_Bill_Party.Party_Name = Rec_Cm.Party_Name
AND Ctt1.Name = Rec_Cm.Trx_Type_Name
AND ((Nvl(Rct.Attribute1
,'-99999') =
Nvl(Rec_Cm.Project_Number
,'-99999') AND p_Project_Flag = 'Y') OR
Nvl(p_Project_Flag
,'N') = 'N')
ORDER BY Rct.Trx_Date
,Aps.Acctd_Amount_Due_Remaining) LOOP
--
IF (l_Cm_Amount > 0) THEN
l_Cm_App_Rec.Cm_Customer_Trx_Id := Rec_Cm.Customer_Trx_Id;
l_Cm_App_Rec.Inv_Customer_Trx_Id := Rec_Inv.Customer_Trx_Id;
Log('l_Cm_Flag:' || l_Cm_Flag);
IF (l_Cm_Flag = 'Y') THEN
Log('l_Cm_Amount:' || l_Cm_Amount);
Log('Rec_Inv.Amount:' || Rec_Inv.Amount);
IF (l_Cm_Amount >= Rec_Inv.Amount) THEN
l_Amount := Rec_Inv.Amount;
l_Cm_App_Rec.Amount_Applied := Rec_Inv.Amount;
l_Cm_Amount := l_Cm_Amount -
Nvl(Rec_Inv.Amount
,0);
ELSE
l_Amount := l_Cm_Amount;
l_Cm_App_Rec.Amount_Applied := l_Cm_Amount;
l_Cm_Amount := l_Cm_Amount -
Nvl(Rec_Inv.Amount
,0);
END IF;
l_Cm_App_Rec.Applied_Payment_Schedule_Id := Rec_Inv.Payment_Schedule_Id;
ELSE
l_Amount := Rec_Inv.Amount;
l_Cm_App_Rec.Amount_Applied := Rec_Inv.Amount;
--l_Cm_App_Rec.Applied_Payment_Schedule_Id := Rec_Cm.Payment_Schedule_Id;
l_Cm_App_Rec.Applied_Payment_Schedule_Id := Rec_Inv.Payment_Schedule_Id;
END IF;
l_Cm_App_Rec.Apply_Date := To_Date(p_App_Date
,'YYYY-MM-DD');
l_Cm_App_Rec.Gl_Date := To_Date(p_App_Gl_Date
,'YYYY-MM-DD');
Log('开始核销.........................................');
Ar_Cm_Api_Pub.Apply_On_Account(p_Api_Version => 1
,p_Init_Msg_List => Fnd_Api.g_True
,p_Commit => Fnd_Api.g_False
,p_Cm_App_Rec => l_Cm_App_Rec
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data
,x_Out_Rec_Application_Id => l_Out_Rec_Application_Id
,x_Acctd_Amount_Applied_From => l_Acctd_Amount_Applied_From
,x_Acctd_Amount_Applied_To => l_Acctd_Amount_Applied_To
,p_Org_Id => p_Org_Id);
IF x_Return_Status <> Fnd_Api.g_Ret_Sts_Success THEN
FOR i IN 1 .. x_Msg_Count LOOP
/* Fnd_Msg_Pub.Get(p_Msg_Index => i
,p_Data => l_Message_List
,p_Msg_Index_Out => l_Msg_Index_Out);*/
l_Fail_Count := l_Fail_Count + 1;
x_Msg_Data := x_Msg_Data ||
Oe_Msg_Pub.Get(p_Msg_Index => i
,p_Encoded => 'F');
Log('错误：' || x_Msg_Data);
END LOOP;
ELSE
Log('核销成功！');
--插入临时表数据，用于打印
END IF;
BEGIN
SELECT Fu.User_Name
INTO l_User_Name
FROM Fnd_User Fu
WHERE 1 = 1
AND Fu.User_Id = Fnd_Global.User_Id;
EXCEPTION
WHEN OTHERS THEN
l_User_Name := NULL;
END;
IF (x_Return_Status = 'S') THEN
l_Status := '成功';
ELSE
l_Status := '失败';
END IF;
INSERT INTO Cux.Cux_1_Ar_Credit_Trans_Temp
(Temp_Id
,Inv_Trx_Number
,Inv_Trx_Type
,Inv_Project_Number
,Cm_Trx_Number
,Cm_Trx_Type
,Cm_Project_Number
,Account_Number
,Party_Name
,Amount
,Trx_Date
,User_Name
,Status
,Error_Msg
,Created_By
,Creation_Date
,Last_Updated_By
,Last_Update_Date
,Last_Update_Login)
VALUES
(Cux.Cux_1_Ar_Credit_Trans_Temp_s.Nextval
,Rec_Inv.Trx_Number
,Rec_Inv.Trx_Type_Name
,Rec_Inv.Project_Number
,Rec_Cm.Trx_Number
,Rec_Cm.Trx_Type_Name
,Rec_Cm.Project_Number
,Rec_Inv.Account_Number
,Rec_Inv.Party_Name
,l_Amount
,To_Date(p_App_Date
,'YYYY-MM-DD')
,l_User_Name
,l_Status
,x_Msg_Data
,Fnd_Global.User_Id
,SYSDATE
,Fnd_Global.User_Id
,SYSDATE
,Fnd_Global.Login_Id);
END IF; --IF (l_Cm_Amount > 0) THEN
END LOOP;
END LOOP;
IF (l_Fail_Count > 0) THEN
x_Return_Status := Fnd_Api.g_Ret_Sts_Error;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END Process_Request;
