Now By Pressing the Distribution Button.
Enter the Distribution Level details as shown below screen shot.
Once the Account is Generated.
à Navigate to Invoice page Actions .
Enable the Check box Validate and press OK button
Create the Account by Enabling the Create Accounting check box and Final and press OK from the Above Screen.
Once the Account is Generated Run the ‘Invoice Approval Workflow’ .
Navigation : view à Requests
Parameters :
Supplier Name ,Invoice Num .
Once the Program complete successfully, follow the below steps
Navigate : Reports à View Invoice Approval History
You will get the Account Manager details for the Invoice and the notification will be sent to the Approver.
CREATE OR REPLACE FUNCTION XX_GET_APPROVER( p_invoice_id IN NUMBER )
RETURN VARCHAR
IS
l_person_id NUMBER := 0 ;
l_count NUMBER := 0;
V_OPERATING_UNIT VARCHAR2(2000):=NULL;
V_ORG_ID NUMBER := 0;
l_return_val VARCHAR2(200) := NULL;
BEGIN
BEGIN
SELECT COUNT(*)
INTO l_count
FROM ap_invoices_all ail,
ap_suppliers aps
-- po_lookup_codes plc
Where Ail.Vendor_Id = Aps.Vendor_Id
And NVL(Aps.Vendor_Type_Lookup_Code,'XYZ') <>'OUTSOURCING'
-- AND aps.vendor_type_lookup_code <>'OUTSOURCING'
-- and aps.vendor_type_lookup_code = plc.lookup_code
AND ail.invoice_type_lookup_code IN ('PREPAYMENT', 'STANDARD','MIXED')
AND ail.invoice_id = p_invoice_id;
-- and plc.lookup_type <> 'VENDOR TYPE' ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Invoice does not exist :');
END;
DBMS_OUTPUT.PUT_LINE('Invoice does not exist l_count :'||l_count);
IF l_count >= 1 THEN
BEGIN
SELECT org_id
INTO V_ORG_ID
FROM ap_invoices_ALL
WHERE invoice_id=p_invoice_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Invoice orgid does not exist :');
END;
DBMS_OUTPUT.PUT_LINE('Org id Value :'||v_org_id);
BEGIN
SELECT name
INTO V_OPERATING_UNIT
FROM hr_operating_units
WHERE organization_id=v_org_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('operating unit does not exist :');
END;
DBMS_OUTPUT.PUT_LINE('Org id Value :'||V_OPERATING_UNIT);
BEGIN
IF v_operating_unit LIKE 'operations' THEN
SELECT paf1.person_id
INTO l_person_id
FROM HR_ALL_POSITIONS_F HAPF,
PER_JOBS PJB ,
PER_ALL_PEOPLE_F PAF1,
FND_USER FU ,
PER_ALL_ASSIGNMENTS_F PAAF
WHERE SUBSTR(HAPF.name,INSTR(HAPF.name,'.',1)+1,15)='Account Manager'
AND PJB.NAME ='Manager'
AND PAAF.PERSON_ID =PAF1.PERSON_ID
AND HAPF.JOB_ID = PJB.JOB_ID
AND PAAF.JOB_ID =PJB.JOB_ID
AND PAAF.POSITION_ID =HAPF.POSITION_ID
AND (sysdate BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
AND (sysdate BETWEEN paf1.effective_start_date AND paf1.effective_end_date)
AND PAAF.JOB_ID =HAPF.JOB_ID
AND FU.EMPLOYEE_ID =PAF1.PERSON_ID
AND rownum =1
AND SUBSTR(HAPF.name,1,2) IN
(SELECT FLEX_VALUE
FROM FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_VALUES FFV ,
FND_FLEX_VALUES_TL FFVT
WHERE FFVS.FLEX_VALUE_SET_ID=FFV.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE_ID =FFVT.FLEX_VALUE_ID
AND FFVT.LANGUAGE ='US'
AND FLEX_VALUE_SET_NAME ='operations_Company'
AND FFVT.DESCRIPTION ='Operations'
);
elsif v_operating_unit='glass' THEN
SELECT paf1.person_id
INTO l_person_id
FROM HR_ALL_POSITIONS_F HAPF,
PER_JOBS PJB ,
PER_ALL_PEOPLE_F PAF1,
FND_USER FU ,
PER_ALL_ASSIGNMENTS_F PAAF
WHERE SUBSTR(HAPF.name,INSTR(HAPF.name,'.',1)+1,15)='Account Manager'
AND PJB.NAME ='Accountant' --'Manager'
AND PAAF.PERSON_ID =PAF1.PERSON_ID
AND HAPF.JOB_ID = PJB.JOB_ID
AND PAAF.JOB_ID =PJB.JOB_ID
AND PAAF.POSITION_ID =HAPF.POSITION_ID
AND (sysdate BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
AND (sysdate BETWEEN paf1.effective_start_date AND paf1.effective_end_date)
AND PAAF.JOB_ID =HAPF.JOB_ID
AND FU.EMPLOYEE_ID =PAF1.PERSON_ID
AND rownum =1
AND SUBSTR(HAPF.name,1,2) IN
(SELECT FLEX_VALUE
FROM FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_VALUES FFV ,
FND_FLEX_VALUES_TL FFVT
WHERE FFVS.FLEX_VALUE_SET_ID=FFV.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE_ID =FFVT.FLEX_VALUE_ID
AND FFVT.LANGUAGE ='US'
AND FLEX_VALUE_SET_NAME ='Company'
AND FFVT.DESCRIPTION ='glass'
);
Elsif v_operating_unit LIKE 'suretech' THEN
SELECT paf1.person_id
INTO l_person_id
FROM HR_ALL_POSITIONS_F HAPF,
PER_JOBS PJB ,
PER_ALL_PEOPLE_F PAF1,
FND_USER FU ,
PER_ALL_ASSIGNMENTS_F PAAF
WHERE SUBSTR(HAPF.name,INSTR(HAPF.name,'.',1)+1,15)='Account Manager'
AND PJB.NAME ='Manager'
AND PAAF.PERSON_ID =PAF1.PERSON_ID
AND HAPF.JOB_ID = PJB.JOB_ID
AND PAAF.JOB_ID =PJB.JOB_ID
AND PAAF.POSITION_ID =HAPF.POSITION_ID
AND (sysdate BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
AND (sysdate BETWEEN paf1.effective_start_date AND paf1.effective_end_date)
AND PAAF.JOB_ID =HAPF.JOB_ID
AND FU.EMPLOYEE_ID =PAF1.PERSON_ID
AND rownum =1
AND SUBSTR(HAPF.name,1,2) IN
(SELECT FLEX_VALUE
FROM FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_VALUES FFV ,
FND_FLEX_VALUES_TL FFVT
WHERE FFVS.FLEX_VALUE_SET_ID=FFV.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE_ID =FFVT.FLEX_VALUE_ID
AND FFVT.LANGUAGE ='US'
AND FLEX_VALUE_SET_NAME ='child_Company'
And Ffvt.Description ='child'
);
Elsif v_operating_unit='master' THEN
SELECT paf1.person_id
INTO l_person_id
FROM HR_ALL_POSITIONS_F HAPF,
PER_JOBS PJB ,
PER_ALL_PEOPLE_F PAF1,
FND_USER FU ,
PER_ALL_ASSIGNMENTS_F PAAF
WHERE SUBSTR(HAPF.name,INSTR(HAPF.name,'.',1)+1,15)='Account Manager'
AND PJB.NAME ='Manager'
AND PAAF.PERSON_ID =PAF1.PERSON_ID
AND HAPF.JOB_ID = PJB.JOB_ID
AND PAAF.JOB_ID =PJB.JOB_ID
AND PAAF.POSITION_ID =HAPF.POSITION_ID
AND (sysdate BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
AND (sysdate BETWEEN paf1.effective_start_date AND paf1.effective_end_date)
AND PAAF.JOB_ID =HAPF.JOB_ID
AND FU.EMPLOYEE_ID =PAF1.PERSON_ID
AND rownum =1
AND SUBSTR(HAPF.name,1,2) IN
(SELECT FLEX_VALUE
FROM FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_VALUES FFV ,
FND_FLEX_VALUES_TL FFVT
WHERE FFVS.FLEX_VALUE_SET_ID=FFV.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE_ID =FFVT.FLEX_VALUE_ID
AND FFVT.LANGUAGE ='US'
AND FLEX_VALUE_SET_NAME ='master_Company'
AND FFVT.DESCRIPTION ='master'
);
ELSE
l_person_id:=0;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_person_id :=0;
END;
DBMS_OUTPUT.PUT_LINE('person id Value :'||l_person_id);
ELSE
l_person_id :=0;
END IF;
DBMS_OUTPUT.PUT_LINE('Invoice does not exist l_person_id :'||l_person_id);
l_return_val := 'person_id:' ||TO_CHAR(l_person_id);
RETURN (l_return_val);
EXCEPTION
WHEN OTHERS THEN
l_person_id :=0;
Return (L_Return_Val);
End XX_Get_Approver;
